Forum Discussion

croissant's avatar
croissant
Regular Visitor
3 years ago

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...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.com)

(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's avatar
    croissant
    Regular Visitor
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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.