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

Board Regular
Joined
Sep 12, 2014
Messages
57
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

alex1alex

Board Regular
Joined
Sep 12, 2014
Messages
57
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}}
)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,466
Members
409,883
Latest member
asharris90
Top