I have a folder with a growing number of files. How can I transpose each file, then combine them? Thanks!
File 1:
Date | 1/1/2023 | 1/2/2023 |
Amount | $50 | $60 |
File 2:
Date | 1/3/2023 | 1/4/2023 |
Amount | $70 | $80 |
Desired end result:
Date | Amount |
1/1/2023 | $50 |
1/2/2023 | $60 |
1/3/2023 | $70 |
1/4/2023 | $80 |
Solved! Go to Solution.
Table.Combine(List.Transform(Folder.Files("your folder")[Content],each Table.PromoteHeaders(Table.Transpose(Excel.Workbook(_)[Data]{0}))))
Table.Combine(List.Transform(Folder.Files("your folder")[Content],each Table.PromoteHeaders(Table.Transpose(Excel.Workbook(_)[Data]{0}))))
Use the Combine & Transform feature and in the transform sample query select your non-date column(s), right click and choose "Unpivot other columns".
Pat