PowerQuery: How can I concatenate grouped values?

Question

If I have the following table (shown in the image below), how can I write a grouped query that would concatenate the grouped results?

For this example, I'd want to group by the LetterColumn and concatenate the NumberColumn

So the desired results would be:

Marc Pincince · Answer 1 · 2021-12-15T13:30:59.780

68

You can use the GUI to do it this way:

Select your LetterColumn and then Transform / GroupBy:
Select Add Column / Custom Column:
Click opposing arrows at top right of new Custom column to Extract Values from new Custom column:

Remove AllData column.

edited Dec 15 '21 at 13:30

answered May 18 '17 at 22:33

Marc Pincince

4,987
6
19
40

9

Genius! How did you find that [AllData][NumberColumn] syntax? I've been working with this tool for 4 years and never seen that before. – Mike Honey Nov 13 '17 at 12:02
6

I honestly don't recall @MikeHoney, but I might have seen it in a response from marcelbeug in the past. I've learned a heck of a lot from him. Either that...or I just tried it and it worked. ;0) – Marc Pincince Nov 13 '17 at 13:11
7

Holy crap this is genius – Gaspa79 May 10 '18 at 17:56
3

1. This is absolute genius! 2. WTF doesn't Excel just have a simple join function? – Eugene Rosenfeld Sep 07 '18 at 00:48
1

Here it is 2020, and I run into to problems. (I'm using Office 365.) 1. The GUI approach (from @MarcPincince) doesn't work for me, even when I try it on Giffyguy's original data. The column that results from step 2 creates a column that says 'Error' in every row. Maybe Get & Transform has changed? It would be great if we could use GUI... 2. Using the formula provided by @Alejandro in 'Custom Column' works great...BUT my problem is the data I'm trying to concatenate is text, not number. Have tried to adapt the formula accordingly but haven't succeeded...any ideas? – P E Jul 21 '20 at 08:07
@P E, I too am using O365 and I just now worked through the steps exactly as I have shown in my original response, using Giffyguy's original data, and it worked fine. I also switched from [AllData][NumberColumn] to [AllData][LetterColumn] and that worked fine as well. I have no idea why you are experiencing a problem with the GUI. – Marc Pincince Jul 21 '20 at 22:40
@P E, Alternative to using the GUI. You can use this code. Just change _#"Changed Type"_ to the name of the preceding step, change _"LetterColumn"_ to whatever column you want to group by, and change _[NumberColumn]_ to the column you want to concatenate the values of. `= Table.Group(#"Changed Type", {"LetterColumn"}, {{"AllData", each Text.Combine(List.Transform([NumberColumn], Text.From), ","), type number}})` – Marc Pincince Jul 21 '20 at 23:00
2

@MarcPincince, I also used your approach successfully for over a year, but just saw the same issue as @P E in O365. I believe PQ is generating incorrect list expansion. I see this: `each Text.Combine(List.Transform(_, Text.From), ...)` - literally dot dot dot, despite having chosen comma. If I change `...` to `","`, world order is restored... – Jerry Norbury Sep 25 '20 at 06:08
1

Interesting @JerryNorbury. Thanks for sharing that bit about the ... – Marc Pincince Sep 25 '20 at 16:59
Fantastic! Just Fantastic Thanks very much! Good job!!! – Christian Eslabon Oct 18 '20 at 11:51
I tried for a couple of hours to figure out how to do this in DAX. I had some ideas around using PATH. Almost glad I didn't figure it out. This is a great solution. I can see this being used all over the place in my Power BI models. Thanks for the solution. – cromastro Nov 03 '20 at 20:58
Excel newbie here. In the extracting values from the list step, how to extract only the distinct values from the list and concatenate them? – Shiva Govindaswamy Dec 15 '21 at 01:47
@Shiva, click the *Extracted Values* step that results from what you do by following what I showed above and try changing the formula that appears in the formula bar from `= Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text})` to `= Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Distinct(List.Transform(_, Text.From)), ","), type text})`. You should see the *Extracted Values* step in the Applied Steps, on the right side of your screen. Click it, then look at the top of your screen for the formula bar. – Marc Pincince Dec 15 '21 at 13:42

score 18 · Accepted Answer · answered May 18 '17 at 22:19

If your table is Source, and if NumberColumn has the number type, then this will work:

= Table.Group(Source, {"LetterColumn"}, {{"Column", each Text.Combine(List.Transform(_[NumberColumn], (x) => Number.ToText(x)), ","), type text}})

Table.Group does a group by operation, which creates a table made up of all of the rows with the same value in LetterColumn. _[NumberColumn] gives a list of the values in the NumberColumn column in this new table. The List.Transform part turns the numbers into text values, and Text.Combine joins those numbers together, with a comma separating each value.

If you need the surrounding quotes as well, you can do this:

= Table.Group(Source, {"LetterColumn"}, {{"Column", each """" & Text.Combine(List.Transform(_[NumberColumn], (x) => Number.ToText(x)), ",") & """", type text}})

"""" represents the " character, and & combines two text values.

score 1 · Answer 3 · answered Oct 22 '22 at 20:48

It would be nice if the GUI had a "Combine Text" operation. However, there is a small adjustment from operation Sum and selecting the Column:

We get straight to this, almost what we want:

= Table.Group(Source, 
    {"LetterColumn"}, 
    {{"NearlyGoodToGo", each List.Sum([NumberColumn]), type number}})

Now, two changes directly in the code:

Replace List.Sum[NumberColumn] with Text.Combine(List.Transform([NumberColumn], Text.From), ",")
Replace type number with type text

I see this as using the GUI for the hard part, to get a basic template, but then adjusting the code to avoid extra steps with the GUI's All Rows operation and the nested tables it produces.

Demo

let
    Source = Table.FromColumns({{"A","A","A","B","B","C","C","C","C"},{1..9}}, type table[LetterColumn = Text.Type, NumberColumn = Number.Type]),
    #"Grouped Rows" = Table.Group(Source, {"LetterColumn"}, {{"GoodToGo", each Text.Combine(List.Transform([NumberColumn], Text.From), ","), type text}})
in
    #"Grouped Rows"

Results

PowerQuery: How can I concatenate grouped values?

3 Answers3

Linked

Related