Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Combine rows on unique ID

Hello, I have the below table called remcost which has a uniqueID called ddID.

 

 

What I need is based upon the column ddID combine the fields that that have in the column rc-type "labour" into 1 field so it lists each person by the column rc-number (Michael Hegerty, Paul Riley, Stephen Spencer, Matthew Spice) and keeps another column for rc-type "vehicle" which is also in rc-number (EK55 TKJ).

 

I'm adding these to a table view and want 2 seperate columns for the combined labour and then 1 for the vehicle. and this is all showing by ddID

 

any help would be appreciated

 

thanks 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Anonymous

     

    Try the below 2 measures.

    MLabour = CONCATENATEX(Table1,IF(Table1[rc-type]="Labour",Table1[rc-number]),",")

    MLabour = CONCATENATEX(Table1,IF(Table1[rc-type]="Vehicle",Table1[rc-number]),",")

     

    Its close to your expectation, but leading/ trailing "," are displayed , a little tune will do from here.

     

    Thanks
    Raj

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Can you please post the expected output in table format for the given sample data?

     

    Also, please post the sample data in copy- pasteable format so that we dont have to type the data again.

     

    Thanks
    Raj

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Raj, here is what I expect the output to be (I have a few other columns in the output from other tables that hopefully shouldn't cause a problem)

       

      ddIDLabourVehicle
      41461Michael Hegerty, Paul Riley, Stephen Spencer, Matthew SpiceEK55 TKJ

       

      and here is the specific columns from the table 

       

      ddIDrc-numberrc-type
      41461EK55 TKJVehicle
      41461Matthew SpiceLabour
      41461Stephen SpencerLabour
      41461Paul RileyLabour
      41461Michael HagertyLabour

       

      If this is not what you require please advise. My knowledge of Power BI is not brilliant. Sorry

       

      thanks 

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Anonymous

         

        Try the below 2 measures.

        MLabour = CONCATENATEX(Table1,IF(Table1[rc-type]="Labour",Table1[rc-number]),",")

        MLabour = CONCATENATEX(Table1,IF(Table1[rc-type]="Vehicle",Table1[rc-number]),",")

         

        Its close to your expectation, but leading/ trailing "," are displayed , a little tune will do from here.

         

        Thanks
        Raj