How can we group by one column and then need to group other column values in comma separated in DAX-CodePudding

Eg:

Department	Customer Name
CSE	A
CSE	B
IT	D
CSE	A
ECE	C
EEE	B
ECE	F

Output:

Department	Customer Name
CSE	A,B
IT	D
ECE	C,F
EEE	B

CodePudding user response：

Try this DAX measure

Measure = CONCATENATEX ( VALUES ( tbl[Customer] ), tbl[Customer], "," )

CodePudding user response：

You can do it in Power Query by adding these below 5 steps to your table's Advance Query Editor

Please adjust the PREVIOUS_STEP_NAME in the code as per your last step name.

let
    //............,
    //............ all previous steps,
    
    #"Removed Duplicates" = Table.Distinct(#"PREVIOUS_STEP_NAME"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Department"}, {{"all name", each _, type table [Department=nullable text, Customer Name=nullable text]}}),

    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [all name][Customer Name]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"all name"})
in
    #"Removed Columns"

Here is the output-

But, if you wants to keep your base table unchanged, just create a new table pointing the base table as source and perform those above steps.