combinations columns

[marab]

hi, 

I have 5 columns merged in one separated  by " _"

I need to consider the combination of the values in the 5 columns, not the permutations. 

I mean the two objects above should appear the same. 

is there anyone that could help?

Thank you!

[marab]

[v-yingjl · Accepted solution]

Hi @marab ,

Based on your description, you need to duplicate the soure table in power query editor and do some transformations like unpiviot columns, remove duplicates etc. for it to summarize a value table which contains all the roles from the source table. The whole query in power query editor is like this:

let
    Source = Table,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Index Repertitiions"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Index Repertitiions", "Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Close and apply it in power query editor, then create a calculated column in the source table to combine these roles:

Group = 
CONCATENATEX ( 'Value', 'Value'[Value], "_" )

You can also use the optional patameters in CONCATENATEX() to define its order, please refer: CONCATENATEX() 

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mussaenda]

Hi @marab,

If I understood you right,

on the first row,

1st column: 23TDO

2nd column: 4LSC

on the second row,

1st column: 4LSC

2nd column: 23TDO

that's why you have the result above.

Right?

How many combinations you have?

[marab]

Hi thanks!

yes correct. 

I have 5 colums corresponding to categories like roles. 

In each column the objects are names of people. 

each name can play different roles

that means the permutaions of the group can be different, but I am interested in analyzing them together.

So I would need Power BI to see the two lines above like unque as the 5 objects are same even if in a different order

Hope that is clear now!

[Nathaniel_C]

Hi @marab ,
Not sure what you are trying to do here.  Perhaps more explanation and expected outcome?

If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you. 

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Nathaniel