# 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 (Sep 25, 2014)

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 (Sep 25, 2014)

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 (Sep 26, 2014)

GREAT 
Thanks !!!! 

zeno


----------



## Tommy Bak (Dec 7, 2016)

works very nice
Thanks 

Bak


----------

