Combine rows on unique ID

[Anonymous]

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 

[Anonymous]

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]

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)

ddID	Labour	Vehicle
41461	Michael Hegerty, Paul Riley, Stephen Spencer, Matthew Spice	EK55 TKJ

and here is the specific columns from the table 

ddID	rc-number	rc-type
41461	EK55 TKJ	Vehicle
41461	Matthew Spice	Labour
41461	Stephen Spencer	Labour
41461	Paul Riley	Labour
41461	Michael Hagerty	Labour

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

thanks 

[Anonymous · Accepted solution]

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

[Anonymous]

Thank you Raj that is perfect