Transpose table in Power Query multiple date columns

Dear Experts,

I am trying to transpose the report to correct the data table, in google searching I found a solution as below steps I followed but I stuck here due to I am new to Power query. need your help with steps I missed or did something incorrect.

First I used TEXTJOIN to join the Depart name and headers with delimiter (semicolon)
Removed top 1 row
Use first row as headers
Select the first 3 columns
Unpivot other columns (dropdown menu unpivot columns on Transform Tab)
Select [Attribute] column
Split column by delimiter (Semicolon)

but the result is not shown as I required, also the date format is not set. I think I am missing some steps, If someone can help me in this please will appreciate to write down the missing steps as I have 2, 3 more reports that I will correct the reports view by Power query as I found it easy to use.

Please also 0/blank values are not required to show in the power query report.

Thanks in advance

Depending on the data and requirements we can not Unpivot the items. As per my knowledge I have created separate table for each department and then combined all in the end.

However, I could not populate the rejection column, instead the quantity is available in the row.

Main Data format as per requirement

Please Login or Register  to view this content.

Cutting Batch 1

Please Login or Register  to view this content.

Cutting Batch 2

Please Login or Register  to view this content.

Stitching Batch 1

Please Login or Register  to view this content.

Stitching Batch 2

Please Login or Register  to view this content.

Stitching Batch 3

Please Login or Register  to view this content.

Finishing Batch 1

Please Login or Register  to view this content.

Finishing Batch 2

Please Login or Register  to view this content.

Rejections

Please Login or Register  to view this content.

Combining all the tables again

Please Login or Register  to view this content.

Thank you so much @vinitdhuri for your help, really appreciated, when I add more columns of Date and Qty, power query did not get these on refreshing in original file there's many columns of date and qty in each department.

Also in required tab I wished Rejection to be in header. may be a way in power query from which we can do this in single code. I will wait if someone other expert can help in more simple way. It will learning for me also to correct the other reports like this one.

Thank you so much

@JeteMc It is so great, simply WOW! you did it by formulas so nice of you and Thank You so much for your precious help and time. you solved this problem with formula. I will implement it on my actual report.

But actually I am looking for someone expert to help in the power query report to be correct, which I refresh and power query extract all the Original Report tab data and place in the power query report. because I have a huge data and it will updated continue.., with formulas my file will become huge I think?

Also (power query report) it will be learning for me and also for others if someone is stuck in problem like this.
(My request to move my post to the correct section of power query if I posted in the wrong section)

@JeteMc Thank you so much once again.

Hi Rory, Thank you for your time.

1. This is daily entry report department wise. every day received some quantity of specific order which enters in the same row but with the received date.
like below. I only add 3 date and qty columns in each department but in my actual report there's more columns of date and qty in each department. (I merged the starting columns headers only for better understanding, in actual these are not merged, these are in one row.

Image20230719144208.png

The result file of @JeteMc is perfect but I need to know the power query process for this report transformation.

also I have 2 other reports like this but if this one is corrected then I will follow the same process for other 2 reports to correct them.

I think its more clearer. if not please ask me I will describe more to understand easily. I really don't want to waste my expert's precious time.

Thank You.

Originally this is manual entry report, we received on manual paper from cutting/stitching/finishing. that which order number production is how much yesterday and we enter the data in this manual excel report in front of the specific order, this report is manually designed in excel sheet and continued since 2018 till now. but now I am trying to automat this report and if possible to give a good look also to create a dashboard which is not possible from this horizontal report.

Like I have a Order# 1

Cutting produced

18/07/2023 100 EA

Stitching produced

18/07/2023 50 EA

Finishing produced

18/07/2023 100 EA

and we have columns 30 for Date and Qty in each department (Cutting & Stitching & Finishing) may be some orders complete in 10 Days some 15 or 20 Days. so every day production enters in this. and the starting A to H columns will show the details that what is order number/style number/color and what is the price for this order and what is total quantity.

what I wish to> when I enter data in the horizontal report daily and I refresh the PQ tab report so the date wise departmental report presented that in which date how much production of which department.

1. All date columns combine
2. All department wise quantity present in the front of date.

May be the above can help. attached again the blank sample file and below the requirement (PQ table).

Yes in same workbook if the workbook MB's not gone huge.

Yes its possible to rename the headers as you describe. if it makes some easiness you may please proceed to rename in sample file.

Thanks

Wow!!!! Rory You are great Thank you Sooooooo muchhhh..

one problem I notice, the rejection quantity is showing with every row, this should be only with the last finishing date. otherwise the rejection quantity will increase just like 7 quantity is reject but the report show 42 total. can you please check this.

Thank You so much once again

Great, Rory, Thank You so much