Forum Discussion
Combine multiple files with dynamic headers
Hi, I have a multiple sheets in which the headers at are at 2 subsequent rows: ie
I want to compile these sheets such that this will be the resulting query
*Data shown above are just samples. I am really working with 50+ files in a shared folder that I can't modify. I have tried using the transform file where you transpose merge then transpose again, but the method was not dynamic. Thanks.
4 Replies
Hi croissant
You can consider to put these sheets as multiple excel files then put them into a folder. the sheet format is as follows , set them as a table.
Book1:
Book2
Then put the files in a folder
Then you can use folder transform, you can refer to the following link.
(refer to the 'Each Workbook has the data with Different Table names or Sheet Names' in the link)
Then you can transform them to the result .
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi! Thanks for the response but the samples I've shown are just samples. I'm really working with 50+ files in a company folder that I can't modify. The main problem is the different and dynamic headers across two rows that is different per file
Hi croissant
You can try the following code in Advanced Editor
The Folder.files() need to put the path of your folder.
let Source = Folder.Files("C:\Users\xxx\Desktop\yourfoldername"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns1", "Custom", each Table.PromoteHeaders([Transform File], [PromoteAllScalars=true])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Transform File"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2", "Column3", "Avail", "WFH", "Rate", "Grade", "CSAT"}, {"Column1", "Column2", "Column3", "Avail", "WFH", "Rate", "Grade", "CSAT"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Column1", "ID"}, {"Column2", "Name"}, {"Column3", "Date"}}), #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Source.Name"}), #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"ID", Order.Ascending}}) in #"Sorted Rows"Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
=Table.Combine(List.Transform(YourTableList,each Table.RenameColumns(_,List.RemoveMatchingItems(List.Zip({Table.ColumnNames(_),Record.ToList(_{0})}),true,each if _ is logical then _ else _{0}=null or _{0}=""))))
