Combining files with column names only available after processing

Hi PhillipB 

 

Assume that you have excel files like below in a folder, you can use Power Query Folder connector to connect to this folder. 

After connection, you can combine files. It will let you select a file as sample file. 

After combining files, it will generate and group queries into the following structure. Use Enter data feature to add a table "ColumnHeaders" which has all possible column headers and has 0 row data. Just like below. 

In "Transform Sample File" query, filter out the top rows before Results section, promote first row to headers, remove the auto-generated "Changed Type" step, combine the earlier "ColumnHeaders" table to the result. Outcome:

"Transform Sample File" code:

let
    Source = Excel.Workbook(Parameter1, null, true),
    Tabelle1_Sheet = Source{[Item="Tabelle1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Tabelle1_Sheet, [PromoteAllScalars=true]),
    // Remove top rows before results section
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Column2] <> null)),
    // Promote first row as column headers
    #"Promoted Headers1" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    // Remove auto-generated "Changed type" step caused by "promote headers" step
    // Combine ColumnHeaders table to this query
    CombineColumnHeaders = Table.Combine({#"Promoted Headers1", ColumnHeaders})
in
    CombineColumnHeaders

 

In the combined query, remove the auto "Changed Type" column, then you will have all file results combined. 

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!