Forum Discussion

vmaru's avatar
vmaru
Frequent Visitor
5 years ago
Solved

Unable to map comma separated values

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

4 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    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
     
    • vmaru's avatar
      vmaru
      Frequent Visitor
      5 years ago

      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's avatar
        selimovd
        Most Valuable Professional
        5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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