Power Query subtable header concatenate and pivot

[pelowski]

I have a lot of subtables within a grouped dataset I'm trying to flatten out into one row per table but I'm having trouble figuring out how to do it.

Essentially I have a bunch of tables that look similar to this...

Order Type	SKU	Price
New License	Product01	$30.00
Upgrade	Product01U	$20.00
Maintenance	Product01M	$10.00

And I would like to flatten them out to look like this...

New License SKU	New License Price	Upgrade SKU	Upgrade Price	Maintenance SKU	Maintenance Price
Product01	$30.00	Product01U	$20.00	Product01M	$10.00

I can't figure out the order of operations to use to make this transformation.  Any help would be appreciated.

[v-danhe-msft · Accepted solution]

Hi @pelowski ,

You could refer to below code in advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS1SCKksSFXSUQr2DgWSCgFFmcmpCkqxOtFKfqnlCj5AXl4xSD6gKD+lNLnEwBDIVjE20DMwACsKLUgvSkxBUQAyR8UIrsI3MTOvJDUvMS8ZRZUvSJUhRFUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Order Type", type text}, {"SKU", type text}, {" Price ", Currency.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Order Type"}, "Attribute", "Value"),
    #"Transposed Table" = Table.Transpose(#"Unpivoted Other Columns"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"New License", type text}, {"New License_1", type any}, {"Upgrade", type text}, {"Upgrade_2", type any}, {"Maintenance", type text}, {"Maintenance_3", type any}})
in
    #"Changed Type2"

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[pelowski]

Thank you!  The Table.UnpivotOtherColumns is exactly what I needed.

My request was a little ambiguous as far as the "SKU" and "Price" parts being on the next line (I just wanted the column headers separated by a line return) but I just added a Table.CombineColumns with an #(lr) separator and now I have exactly what I want, one header row and one detail row of data.  I'm going to turn this into a function to iterate across all the subtables in the master table.

Thanks again for your help!