Data Transformation Problem

[rashidanwar]

Hi everyone 

Hope that you people are doing great. I am facing a problem as explained below. Any help would be greatly appreciated.

I have the following example data.

order_id       orderline_id        service_type                      country
1                  1                         service_type_1                   Belgium
1                  2                         service_type_2                   France
1                  3                         service_type_3                   Italy
2                  1                         service_type_1                   UK
2                  2                         service_type_2                   USA

Order ID is a unique ID for each order. I want to add another column to combine all the countries under a specific order ID, separated by comma, for each order line.
after adding new column data will look like as follow.
order_id       orderline_id        service_type                      country            new_column
1                  1                         service_type_1                   Belgium           Belgium, France, Italy
1                  2                         service_type_2                   France              Belgium, France, Italy
1                  3                         service_type_3                   Italy                  Belgium, France, Italy
2                  1                         service_type_1                   UK                    UK, USA
2                  2                         service_type_2                   USA                  UK, USA

Thank you so much.

[ryan_mayu]

@rashidanwar 

you can create a new column

Column = 
CONCATENATEX(FILTER('Table','Table'[order_id ]=EARLIER('Table'[order_id ])),'Table'[country],",")

[rashidanwar]

Thank you @ryan_mayu for your quick response.
There is a problem with existing solution. The problem is that if the country is repeated against a single order Id. Like if in above example for order id 2 , service_type_3 the country is again France there output will be Belgium, France, France.

But the intended result shoul be Belgium, France

Can you please help figure out this?

[ryan_mayu · Accepted solution]

@rashidanwar 

pls try this

Column = 
var tbl=SUMMARIZE('Table','Table'[order_id],'Table'[country])
return CONCATENATEX(FILTER(tbl,'Table'[order_id]=EARLIER('Table'[order_id])),'Table'[country],",")

[rashidanwar]

Thank you so much @ryan_mayu 

[ryan_mayu]

you are welcome