Power Query: Concatenate All Rows

Question

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:

Check out edureka.co/community/39337/… and radacad.com/… — Gitty

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2020-05-08T10:32:58

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"

Power Query: Concatenate All Rows

1 Answers