Transform multi row header from Excel Table in Power Query

[adelmonte]

Hi all,

Would someone give me a tip on how I could prepare this Excel file in power query instead of preparing each file manually.

Excel Sample file
https://bit.ly/3IjSlHn

Thanks in advance.

Regards,
Alex

[AlexisOlson · Accepted solution]

The trick is to do it in a few pieces and then stick them together. For example, I'd do this in three pieces.

#1 Company:

let
    Source = [#"Company ID" = Table1{0}[Column4], #"Company" = Table1{0}[Column5]],
    #"Converted to Table" = Record.ToTable(Source),
    #"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value")
in
    #"Pivoted Column"

#2 Date+IDs:

let
    Source = Table1,
    #"Kept First Rows" = Table.FirstN(Source,3),
    #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Other Columns",{"Column3", "Column4", "Column5"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Column1]), "Column1", "Column2"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type datetime}, {"Internal ID", Int64.Type}, {"Substitute ID", Int64.Type}})
in
    #"Changed Type"

#3 Delivery + #1 + #2:

let
    Source = Table1,
    /*Extract Delivery data*/
    #"Removed Top Rows" = Table.Skip(Source,5),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Delivery ID] <> null)),
    /*Add in Company table*/
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Company),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Company ID", "Company"}, {"Company ID", "Company"}),
    /*Add in Date+IDs table*/
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each #"Date+IDs"),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Date", "Internal ID", "Substitute ID"}, {"Date", "Internal ID", "Substitute ID"}),
    /*Cleanup*/
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom1",{"Date", "Internal ID", "Substitute ID", "Company ID", "Company", "Delivery ID", "Order Nr", "Truck", "Article", "Article Desc", "Content", "Pallet Desc", "Sub Article", "Origin", "Category", "C/C"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}, {"Internal ID", Int64.Type}, {"Substitute ID", Int64.Type}, {"Company ID", Int64.Type}, {"Company", type text}, {"Delivery ID", Int64.Type}, {"Order Nr", type text}, {"Truck", Int64.Type}, {"Article", Int64.Type}, {"Article Desc", type text}, {"Content", type text}, {"Pallet Desc", Int64.Type}, {"Sub Article", Int64.Type}, {"Origin", type text}, {"Category", type text}, {"C/C", Int64.Type}})
in
    #"Changed Type"

See attached:

[adelmonte]

Hi Alexis,

Your solution works perfectly well if applied to single file.
Would you be able to suggest me how could I handle multiple files (from folder) with the same structure but different data.

Thanks in advance.
Alex

[AlexisOlson]

You can load files from a folder and combine the queries I suggested into a single table function that you apply to each table you want to load.

Start here to get an idea of what I mean:
https://www.howtoexcel.org/power-query/how-to-import-all-files-in-a-folder-with-power-query/

[adelmonte]

Thanks Alexis

[adelmonte]

Many thanks Alexis.

[Anonymous]

I would suggest to create tables in excel as power query recognises table very easily.