Combine multiple rows and merge to 1 column

[Pbi07]

What is the best way out to merge multiple rows and combine the values into a single column.

Partner   Order    ID   Comments
1575       8001     1     Partner call
1575       8001     2    Order book
1575       8001     3    Order Ship
1580       8005     1    Quote request
1582       8020     1    Partner request
1582       8020     2    Order confirm

End result need to be as below. The ID column value range from 1 to N. 

Partner   Order    Comments
1575       8001     Partner call;Order book;Order Ship
1580       8005     Quote request
1582       8020     Partner request; Order confirm

[v-lili6-msft · Accepted solution]

hi @Pbi07 

For your case, you could try these two way:

1. use dax to create a new table as below:

Table (By dax) =
SUMMARIZE (
    'Table',
    'Table'[Partner],
    'Table'[Order],
    "Comments", CONCATENATEX ( VALUES ( 'Table'[Comments] ), [Comments], ";" )
)

Result:

2. in edit queries, group by the table as below:

= Table.Group(#"Changed Type", {"Partner", "Order"},  {"Comments", each Text.Combine([Comments], ";"), type text})

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[ccarpent]

Hi Lin

I found this post very helpful, in my situation I am trying to convert my table view from lookig like this  -  

CostCenter	Project Leader1	CC_prefix	BudgetName	Project_ID	CostCenter2
456ABC	Chris Harp	456	Thomas Bell	78787899	777ABC
456XYZ	Chris Harp	466	Peter Welling	78787899	777ABC

to look something like this - 

CostCenter_combined	Project Leader1	CC_prefix	BudgetName_combined	Project_ID	CostCenter2
456ABC | 466XYZ	Chris Harp	456	Thomas Bell | Peter Welling	78787899	777ABC

So the Project_ID is unique, the reason for the top version is because there are two members working on the same project but I want to show in my report one project_id at 'X' value.  As it stands the a top table produces the total project value x 2 when it only should be one value.  So if the value was 1000.00 GBP, the the table at the top would show 1000.00 GBP twice, when it needs to be only once.  

Chris

[Pbi07]

Thank you. Perfect solution.