Forum Discussion
Ray_Brosius
Helper III
Helper IIIGrouping 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 invoice and a column that has the products in a csv string.
how do I do this?
| Invoice | Product |
| 1 | a |
| 1 | b |
| 1 | c |
End u pwith
Inovice
| Invoice | Product |
| 1 | a,b,c |
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"
1 Reply
jgeddesSuper User
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"