Forum Discussion
Solved
Transpose tables before combining
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 |
Table.Combine(List.Transform(Folder.Files("your folder")[Content],each Table.PromoteHeaders(Table.Transpose(Excel.Workbook(_)[Data]{0}))))
2 Replies
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
Table.Combine(List.Transform(Folder.Files("your folder")[Content],each Table.PromoteHeaders(Table.Transpose(Excel.Workbook(_)[Data]{0}))))
