I am looking to concatenate rows instead of columns. I can do this by pivoting and merging columns, but that process is not repeatable as the rows change (the column merging then hardcodes the values). How would I do a GroupBy function but concatenate rather than sum/count/etc?
NOTE: It's worth noting that the text must stay as a Text type. The prices are sometimes $275pp or something and I need to maintain the letters
A visual of what I am trying to do is below:
You just need to Group by Group and do some manipulations to add the quotation marks and brackets:
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Price", type text}, {"Group", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "quotPrice", each """" & [Price] & """"),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Group"}, {{"Grouped", each _, type table [Price=text, Group=number, quotPrice=text]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Grouped],"quotPrice")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Added Custom2" = Table.AddColumn(#"Extracted Values", "Text", each "[" & [Custom] & "]"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Grouped", "Custom"})
in
#"Removed Columns"
The answer you're looking for is to combine a Table.Group() function with a Text.Combine() function.
This will work for you:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUjEyN1XSUTJUitUBcowNDBAcQyOQjBGEYwSWgXIMzZFkLMEyxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Price = _t, Group = _t]),
#"Grouped rows" = Table.Group(Source, {"Group"}, {{"Count", each "[""" & Text.Combine([Price], """, """) & """]", type text}})
in
#"Grouped rows"
