Excel/Power Query - Combining Identically formatted data from multiple sheets in a particular way

[Excel Jason]

Hello!

I have an interesting challenge that I'm looking to "gauge the room" on the most efficient approach to solve it. I suspect Power Query may be the winner but need brighter minds than my own.

I have 52 files containing identically formatted (but differnet data). I need to combine all 52 files into a single table thats manipulated as such:

The dates and quantities are transposed and the item numbers are repeated as many times as there are unique dates for each item number. I would then like the second, third, fourth, etc.. file to add it's data beneath the previous files data vertically. My files are relatively large so I suspect this to be a massive (900k Row) file.

Hopefully I'm making sense - please let me know if you need clarification!

Data Example:

ITEM	Descr	LOC	10-24-22	10-31-22	11-07-22	11-14-22	11-21-22	11-28-22	12-05-22	12-12-22	12-19-22
10221463	Unknown	Unknown	8948​	4270​	4745​	6399​	4664​	9654​	3361​	6373​	6735​
10224680	Unknown	Unknown	6361​	6466​	784​	752​	3161​	3584​	5123​	345​	3404​
10224681	Unknown	Unknown	4630​	510​	8185​	130​	142​	4065​	8286​	9430​	3337​
10184291	Unknown	Unknown	5465​	7065​	5708​	7190​	4800​	2419​	6292​	3202​	2895​
10239929	Unknown	Unknown	6121​	9585​	2691​	6742​	2305​	9172​	7475​	7848​	9082​
10239930	Unknown	Unknown	6447​	3559​	548​	61​	8006​	5972​	5685​	7854​	188​
10245568	Unknown	Unknown	6278​	7723​	3969​	2382​	5338​	4132​	1507​	3307​	4061​
10249906	Unknown	Unknown	9255​	6889​	4527​	2722​	3252​	4188​	6923​	9863​	5388​
10250138	Unknown	Unknown	5820​	3979​	5814​	4033​	8340​	4126​	8536​	1452​	3450​
10267670	Unknown	Unknown	2026​	496​	8408​	2141​	5059​	3238​	1910​	4242​	8276​
10233093	Unknown	Unknown	9736​	9361​	9720​	1722​	2960​	8419​	761​	1961​	427​
10233103	Unknown	Unknown	6251​	2942​	3236​	3869​	2814​	487​	2913​	192​	837​
10238629	Unknown	Unknown	7376​	6575​	502​	5133​	3348​	6055​	346​	4265​	2143​
10239792	Unknown	Unknown	3901​	4260​	3062​	3724​	7666​	3490​	9530​	4031​	7878​
10251121	Unknown	Unknown	295​	4614​	8761​	1753​	6031​	5225​	7667​	9703​	8423​
10268674	Unknown	Unknown	3201​	1997​	7512​	6018​	6239​	1171​	7100​	3147​	4558​

 

[alansidman]

Have all files in one folder and then load them into pq and transform append. The key will be to have them in one folder.

 

[Excel Jason]

Hey Alan, thanks for the tip! Yes, pq all the way; However, could you please provide a few steps to perform in pq that would align my data the way I displayed it above?

The root cause seems to be the complexity around the SKU numbers. Those aren't transposed they're duplicated as many times as there are dates (which need to be transposed) along with their respective volume numbers (also transposed).

Hopefully I'm making sense, thank you!

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ITEM", "Descr", "LOC"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"