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 in Power BI by Upasana
• 8,530 points
187 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 by Shubham
• 13,290 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,530 points
49 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 in Power BI by Cherukuri
• 25,900 points
453 views
0 votes
1 answer

How many reports can be created in one Power BI reports

You have a limit of 1000 datasets ...READ MORE

answered Sep 24, 2018 in Power BI by Hannah
• 14,070 points
45 views
0 votes
1 answer

Can I create a Power BI report in Office 365 without my locally installed Office Excel?

Yess!! You can achieve this by using ...READ MORE

answered Sep 26, 2018 in Power BI by Kalgi
• 40,460 points
68 views
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,550 points
44 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
• 56,520 points
47 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
• 56,520 points
1,505 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,190 points
196 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 in Power BI by Shubham
• 13,290 points
330 views
0 votes
1 answer

Can I get rid of the grey border on the report visuals with power bi embedded?

You can do the following to make the ...READ MORE

answered Apr 10 in Power BI by Shubham
• 13,290 points
268 views