In Power Query, how do you Group a table by a column and then concatenate instead of a SUM for one of the columns?

[alex1alex]

Hello,

In the end I'm trying to get something like this.
start with table
A B C
1 a 1
1 b 2
2 c 3
3 d 4
end with table
A CatOfB
1 a:b
2 c
3 d
(where CatofB is a concatination of all B column fields that match column A)

using this as a start:
aTable = #table({"A","B","C"},{{1,"a",1},{1,"b",2},{2,"c",3},{3,"d",4}}),

i can do a sum of C:
aGroupRowsSum = Table.Group(
aTable,
{"A"},
{{" Sum of C", each List.Sum([C]), type number}}
),

where aGroupRowsSum =
A SumOfC
1 3
2 3
3 4

But i can't figure out how to do the concatenate of B.
This seems close but no cigar:
fCombine = Combiner.CombineTextByDelimiter(":"),
aGroupRowsCat = Table.Group(
aTable,
{"A"},
{{"CatOfB", fCombine (each ([C])), type text}}
)



Any suggestions?

Thanks,
Alex

 

[alex1alex]

Arg, I didn't understand how the each works.

This works great, bold is what I changed :

aGroupRowsCat = Table.Group(
aTable,
{"A"},
{{"CatOfB", each fCombine([C]), type text}}
)

 

[zeno2]

GREAT
Thanks !!!!

zeno

 

[Tommy Bak]

works very nice
Thanks

Bak