Power Query - Combine values in rows into one single cell CSV

[Aldonin]

Hi there Gurus.

I have a large data sate in the form below where same login appears multiple time, one for each different location.

LOGIN	LOCATIONS
PETER	Loc1
JOHN	Loc1
PETER	Loc3
CHARLES	Loc2
CHARLES	Loc7
JOHN	Loc5
PETER	Loc9

I need to transform it into something that only has the login once, and aggregates all locations using comma as delimiter, (using power query)

LOGIN	LOCATIONS
PETER	Loc1,Loc3,Loc9
JOHN	Loc1,Loc5
CHARLES	Loc2,Loc7

I tried merging columns and grouping without success as the login gets repeated after every LocX code.

Any help is much welcome, and... thank you!

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"LOGIN"}, {{"Count", each _, type table [LOCATIONS]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][LOCATIONS]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Accumulate([Custom], "", (state, current) => state & current & "," )),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Text.Middle([Custom.1],0,Text.Length([Custom.1])-1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Count", "Custom", "Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.2", "LOCATIONS"}})
in
    #"Renamed Columns"

Book4
	A	B	C	D	E
1	LOGIN	LOCATIONS		LOGIN	LOCATIONS
2	PETER	Loc1		PETER	Loc1,Loc3,Loc9
3	JOHN	Loc1		JOHN	Loc1,Loc5
4	PETER	Loc3		CHARLES	Loc2,Loc7
5	CHARLES	Loc2
6	CHARLES	Loc7
7	JOHN	Loc5
8	PETER	Loc9
Sheet1

 

[alansidman]

This can also be acccomplished in Power Pivot. Here is a link to a tutorial on how to create a DAX Measure using Concatenatex

IMA Site Maintenance

 

[XL Pro]

Just pointing out that the List.Accumulate step is unnecessary... once you have the list, you can extract it into values:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"LOGIN"}, {{"Count", each _, type table [LOCATIONS]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][LOCATIONS]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Other Columns" = Table.SelectColumns(#"Extracted Values",{"LOGIN", "Custom"})
in
    #"Removed Other Columns"

Though if you want to get really tricky, you can combine the List & Extract into the Grouping:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"LOGIN"}, {{"Locations", each Text.Combine(List.Transform(_[LOCATIONS], Text.From), ",")}})
in
    #"Grouped Rows"

 

[JGordon11]

Just pointing out that the List.Accumulate step is unnecessary... once you have the list, you can extract it into values:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"LOGIN"}, {{"Count", each _, type table [LOCATIONS]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][LOCATIONS]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Other Columns" = Table.SelectColumns(#"Extracted Values",{"LOGIN", "Custom"})
in
    #"Removed Other Columns"

Though if you want to get really tricky, you can combine the List & Extract into the Grouping:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"LOGIN"}, {{"Locations", each Text.Combine(List.Transform(_[LOCATIONS], Text.From), ",")}})
in
    #"Grouped Rows"

Thanks - that's a much better way (especially your second one)

 

[GraH]

or almost the same, without the List.Transform.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"LOGIN"}, {{"Locations", each Text.Combine([LOCATIONS],","), type nullable text}})
in
    Group

 

[XL Pro]

Yup - I got a little ahead of myself when I combined the steps . Nice catch!

 

[GraH]

If it is a large table, maybe sort it, add index, then use the Fourth parameter in the grouping Group.KindLocal to speed things up. I did not test, but it could be beneficial.

 

[GraH]

Yup - I got a little ahead of myself when I combined the steps . Nice catch!

Thx, but it's a trick @MrExcel showcased a few months ago.