Forum Discussion

Ray_Brosius's avatar
Ray_Brosius
Helper III
2 years ago
Solved

Grouping in Power Query

I have a simple two column table invoice and Product   there are mutliple products per invoice so there is a record per invocie and product combination   I want to end up with one record per inv...
Need Help
Tips and Tricks
  • jgeddes's avatar
    jgeddes
    2 years ago

    Consider the table...

    You can get the result...

    With the code...

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWBsJLgrGQwywguawQXMwayUpRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Invoice = _t, Product = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Invoice"}, {{"Product", each _, type table [Invoice=nullable text, Product=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Product], "Product")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Product"})
    in
    #"Removed Columns"
jgeddes's avatar
jgeddes
Super User
2 years ago

Consider the table...

You can get the result...

With the code...

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWBsJLgrGQwywguawQXMwayUpRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Invoice = _t, Product = _t]),
#"Grouped Rows" = Table.Group(Source, {"Invoice"}, {{"Product", each _, type table [Invoice=nullable text, Product=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Product], "Product")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Product"})
in
#"Removed Columns"