Pivot columns with multiple criteria in Power Query-CodePudding

I have the below source data -

I'm trying to transform it into -

I have already tried pivoting the Type column in transform tab but this results into multiple rows for each SKU with values for Length, Height and Width in the next rows.

How can I pivot the data so that I have all the Headers as column names with the unit merged into the value for each header?

CodePudding user response：

Starting with this table in excel, named Table1:

I use this M code...

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"Type", type text}, {"Value", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Type"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Modulo", "Custom", each if Type.Is(Value.Type([Value]),type number) then Text.From([Value]) & " " & #"Inserted Modulo"{[Index]-1}[Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Modulo] = 0)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Modulo"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Value]), "Value", "Custom")
in
    #"Pivoted Column"

to get this result:

CodePudding user response：

Try this

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "IsHeader", each if [Type]="Header" then [Index] else null),
#"Added Custom3" = Table.AddColumn(#"Added Custom", "Custom2", each if [Type]="Header" then [Value] else null),
#"Filled Down" = Table.FillDown(#"Added Custom3",{"IsHeader", "Custom2"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Type] <> "Header")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Type]), "Type", "Value"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Pivoted Column", {{"Value", type text}}, "en-US"),{"Value", "Unit"},Combiner.CombineTextByDelimiter( " ", QuoteStyle.None),"Merged"),
#"Removed Columns1" = Table.RemoveColumns(#"Merged Columns",{"IsHeader"}),
#"Pivoted Column1" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Custom2]), "Custom2", "Merged")
in  #"Pivoted Column1"