Merge or Group Columns?

[sharpedogs]

Hi, 

I have the below dataset, I essentially want to flatten the dataset so that instead of having multiple lines showing each AD Group Chad and David have, I want to merge the AD Groups so that the end result is a column with Chad and a column with (Office, Exchange).

User	AD GROUP
CHAD	OFFICE
CHAD	EXCHANGE
DAVID	OFFICE
DAVID	POWERBI

[Anonymous · Accepted solution]

try following these step:

having loaded your table, select group by function:

and edit and pick the field, so that have this situation:

you get this table with error. You have then edit and correct the formula in the bar-formula:

changint List.Sum([AD_GROUP]) with Text.Combine([AD_GROUP],","):

after edited click enter. You should get this table athis poitn:

[v-eachen-msft · Accepted solution]

Hi @sharpedogs ,

You could use "pivot" function with Text.Combine() to get the result. Then unpivot these columns to get final result.

 

Here is my test file for your reference.

[Anonymous]

#"Grouped Rows" = Table.Group(#"Changed Type", {"User"}, {{"list_ad", each _[AD GROUP]}}),
#"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"list_ad", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
#"Extracted Values"

[sharpedogs]

I appriciate the reply, but your level of PowerBi is way beyond my understanding :)....  I'm not sure where I start to enter your resolution? Is it an Add Column? Are you able to lay it out in more detailed steps?

[Anonymous · Accepted solution]

try following these step:

having loaded your table, select group by function:

and edit and pick the field, so that have this situation:

you get this table with error. You have then edit and correct the formula in the bar-formula:

changint List.Sum([AD_GROUP]) with Text.Combine([AD_GROUP],","):

after edited click enter. You should get this table athis poitn: