PowerQuery: How can I concatenate grouped values?

excel group-by powerquery excel-2016 m
Giffyguy picture Giffyguy · May 18, 2017 · Viewed 18.5k times · Source

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

InputTable

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

So the desired results would be:

ResultsTable

Answer

Marc Pincince picture Marc Pincince · May 19, 2017

You can use the GUI to do it this way:

  1. Select your LetterColumn and then Transform / GroupBy: enter image description here

  2. Select Add Column / Custom Column: enter image description here

  3. Click opposing arrows at top right of new AllData column to Extract Values from new AllData column:

    enter image description here enter image description here

  4. Remove AllData column.

Related questions

Excel - How do I create a cumulative sum column within a group?

In Excel, I have an hours log that looks like this: PersonID Hours JobCode 1 7 1 1 6 2 1 8 3 1 10 1 2 5 3 2 3 5 2 12 2 2 4 1 What I would like to do is create a column with a running total, but only within each PersonID so I want to create this: PersonID …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More