Forum Discussion

marab's avatar
marab
New Member
5 years ago
Solved

combinations columns

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!

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

5 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    5 years ago

    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

  • mussaenda's avatar
    mussaenda
    Community Champion
    5 years ago

    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's avatar
      marab
      New Member
      5 years ago

      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!

    • v-yingjl's avatar
      v-yingjl
      Community Support
      5 years ago

      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.