Combine multiple files with dynamic headers

[croissant]

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.

[wdx223_Daniel]

=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}=""))))

[Anonymous]

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.

Combine Data from Multiple Workbooks in Excel (using Power Query) [5296-9a87-434-44a7] (trumpexcel.c...

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

[croissant]

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

[Anonymous]

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.