I'm stuck with trying to group multiple rows into one row, in Excel Power Query.

The table I'm working with is listed as below:

How I want it to be displayed

Is there an easy process on how to display multiple rows in one single row? I have tried to group on product id and then merge data. But I would like to try to get the result like above.

Thanks!

CodePudding user response：

Assume the table name is Table1:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(
                Source,
                "Product ID",
                {
                    "a",
                    each let 
                            a =Table.ToRows(Table.RemoveColumns(_,"Product ID")),
                            b = List.Accumulate(
                                    {0..List.Count(a)-1},
                                    {},
                                    (s,d)=>s &{"Purchase ID " & Text.From(d 1),"Amount " & Text.From(d 1)}
                                ),
                            c =#table({"Product ID"} & b,{{_[Product ID]{0}} & List.Combine(a)})
                        in c
                }
),
    Res = Table.Combine(Group[a])
in
    Res

CodePudding user response：

Another method, relying on unpivoting. Has the benefit of directly working on any number of columns adjacent to ProductID

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source , {"Product ID"}, "Attribute", "Value"),
#"Group"= Table.Group(#"Unpivoted Other Columns", {"Product ID"}, {{"GRP", each Table.TransformColumns(Table.AddIndexColumn(_, "Index", 0, (1/(List.Count(Table.ColumnNames(Source))-1))),{{"Index", each Number.RoundTowardZero(_), type number}}), type table}}),
#"Expanded GRP" = Table.ExpandTableColumn(#"Group", "GRP", {"Attribute", "Value", "Index"}, {"Attribute", "Value", "Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded GRP", "Custom", each [Attribute]&Text.From([Index])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value", List.Sum)
in #"Pivoted Column"