Unable to map comma separated values

[vmaru]

I have a problem that I am trying to solve. I have table A with ID column that contains values which are comma separated. I also have Table B that contains the ID and the text columns. I am trying to map ID from Table A with ID from Table B so that I can populate the country as shown under Expected Results. When I map the ID columns from both the tables, I am able to get the country for single ID but it does not work for comma separated ID’s and thereby the value is blank. I would appreciate if someone can help me fix this issue. 

Table A        

ID

1

2

3,4

5,6,7

8

9

Table B

ID          Country

1             Canada

2             India

3             France

4             USA

5             Mexico

6             Brazil

7             Germany

8             Australia

9             Japan

Expected Result:

Canada

India

France, USA

Mexico, Brazil, Germany

Australia

Japan

[v-yiruan-msft · Accepted solution]

Hi @vmaru ,

You can create a calculated column as below to get it, please find the details in the attachment.

Column = 
VAR _id =
    SUBSTITUTE ( 'A'[ID], ",", "|" )
VAR _len =
    LEN ( _id )
VAR _tab =
    ADDCOLUMNS (
        GENERATESERIES ( 1, _len ),
        "idlist", VALUE ( PATHITEM ( _id, [Value] ) )
    )
VAR _idlist =
    SELECTCOLUMNS ( _tab, "list", [idlist] )
RETURN
    CONCATENATEX ( FILTER ( 'B', 'B'[ID] IN _idlist ), 'B'[Country], ", " )

Best Regards

[selimovd]

Hey @vmaru ,

why don't you split the comma separated values from Table A into multiple rows?

Is there a reason that you want to keep that in  single cell? Comma separated IDs are always difficult to analyze.

You can easily split the values into new rows in Power Query:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[vmaru]

The comma seperated values in Table A comes from a multi select option set field in Dynamics 365. If there is multiple selections for the field in D365, they come as comma seperated values into Power BI. So, I cannot split the values from Table A into multiple rows.

[selimovd]

@vmaru Sorry, I didn't understand that. Why can't you split the comma separated values into new lines?