How can I concatenate grouped values in PowerQuery ?

0 votes

If I have this table, how can I write a grouped query that would concatenate the grouped results?

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

So, the desired output is:

Feb 27, 2019 in Power BI by Upasana
• 8,620 points
1,246 views

1 answer to this question.

0 votes
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.
answered Feb 27, 2019 by Shubham
• 13,450 points

Related Questions In Power BI

0 votes
1 answer

How can i see my plugin name in the Power BI Visual plugin List ?

there is a couple of errors in your ...READ MORE

answered Nov 8, 2018 in Power BI by Upasana
• 8,620 points
162 views
0 votes
1 answer

How can I get a column value from previous row in Power Query?

Hi Sindhu, add this line as your ...READ MORE

answered Mar 18, 2019 in Power BI by Cherukuri
• 32,490 points
4,573 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,680 points
153 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 57,680 points
153 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 57,680 points
2,525 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,200 points
1,525 views
0 votes
1 answer

PowerQuery : How can I reference a cell's value?

You can do so using a named ...READ MORE

answered Feb 27, 2019 in Power BI by Shubham
• 13,450 points
7,994 views
0 votes
2 answers