question

CarlYuanGLiu-4866 avatar image
0 Votes"
CarlYuanGLiu-4866 asked Lz-3068 commented

Collapsing the values of a field in a Group into a single long text

A table can be divided into Groups of rows by the value of a key field.
Normally, one can collapse the values of a field in each Group into a single statistic, such as Total or count.
But I need to collapse the Groups of a field in a table into a list. That is, to collect the values of a text field belonging to a Group, and concatenate them into a single text.
Let me explain.
I have a table that, say, looks like this:
Group Word
1 bee
2 cee
2 dee
2 deed
3 cede
3 ace
3 dace
3 bade
4 babe

I would like to create a new table that looks like this:
Group WordList
1 bee
2 cee, dee, deed
3 cede, ace, dace, bade
4 babe

Perhaps just a subquery in a Select statement would do it?
I have looked at doing this in Excel, Access, VBA, and, finally, Power Query.
To no avail.
A solution in Power Query (my new toy) is fine, but any other tool is OK also.
I would hate to resort to manual labor, as I have a total of 6600 words grouped in about 640 Groups. And I may have to do this a couple of times, as the structure might change in the future.

Help me, oh, please help me.

power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Lz-3068 avatar image
0 Votes"
Lz-3068 answered Lz-3068 commented

Hi @CarlYuanGLiu-4866

One way:

 let
     Source = Table.FromColumns(
         { {1,2,2,2,3,3,3,3,4,4}, {"abc","bcd","def","ghi","jkl","mno","pqr","stu","vwx","yza"} },
         type table [Group=number, Word=text]
     ),
     WorldList = Table.Group(Source, {"Group"},
         {
                 {"WordList", each Text.Combine([Word], ", "), type text}})
 in
     WorldList
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Wow, so fast and right to the point. Saves my day. Thank you!

0 Votes 0 ·
Lz-3068 avatar image Lz-3068 CarlYuanGLiu-4866 ·

@CarlYuanGLiu-4866. Glad I could help (easy one) & Thanks for posting back

When Grouping rows the User Interface offers you a few aggregation options (Count, Sum...). If you go to the Advanced Editor you can do a lot more...

0 Votes 0 ·