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 ar...
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.