Replace comma separated IDs in a column from table 1 with values from table 2

[Anonymous]

I have two tables where table 1 acts as a fact table with a column that contains the IDs from another table. Each row can contain no ID, one ID, or more than one ID separated by a comma. The second table acts as a dimension table where column 1 is the unique ID and column 2 is the unique value for that ID. This table is dynamic as rows can be added should new values be needed. There are around 20 or so unique IDs/Labels. At this point, the most lables placed in a single row is five, but that could change based on business need. 

Help:

What I am looking for is how to stand up a column in table 1 that returns the values for the ID(s) in that row's column. 

What I have tried:

When I manage the relationships between the two tables, joining on ID columns, this works only for those rows where there is one ID in table 1. Anytime there is more than one ID in a field, nothing gets returned. I have tried using some DAX formulas as well as power query but can't seem to return values where there is more than one ID.

Here are three tables to show what I have and what I would like to accomplish:

Table 1

Card_ID	Card_Label_ID
abc123	a1s2d3f4
def456	b5n6n7m8, a1s2d3f4
ghi789

Table 2

Label_ID	Label_Value
a1s2d3f4	Bug
b5n6n7m8	Maintenance
c0v9b8n7	Enhancement

Table 3 - Desired Output for Table 1

Card_ID	Card_Label_ID	Card_Label_Value
abc123	a1s2d3f4	Bug
def456	b5n6n7m8, a1s2d3f4	Maintenance, Bug
ghi789

Any help would be greatly appreciated!

Thanks

[sanalytics]

@sam123,

Since Greg sir has already posted DAX solution..So,you can get gelp from that..Although below is the Power query solution

https://www.dropbox.com/s/oxag35s267yn01p/Replace%20comma%20separated%20IDs%20in%20a%20column%20from...

downlaod the PBI file..
Regards,

sanalytics

[Greg_Deckler · Accepted solution]

@Anonymous See what you think of this solution. PBIX is attached after sig. Table (4) and Table (4a):

Column = 
VAR __Table =
    ADDCOLUMNS(
        GENERATE(
            { [Card_ID] },
            'Table (4a)'
        ),
        "Replace",IF(FIND([Label_ID],[Card_Label_ID],,0)>0,[Label_Value],BLANK())
    )
RETURN
    CONCATENATEX(FILTER(__Table,NOT(ISBLANK([Replace]))),[Replace],", ")

[Anonymous]

Hey Greg,

Thank you so much! This works perfectly. Providing the file was incredibly helpful to understanding the logic (which is gonna take a me little to work through despite yee ole copy and paste working like a charm).

Best,

Sam

[Greg_Deckler]

@Anonymous 

So, create a table variable called __Table. Use the current row's value of the column Card_ID, treating it as a table ( {   } ) and then using this generate a table that contains what is essentially a cartesian product between this and Table (4a) so you end up with a table that includes the current row's Card_ID value along with each row from Table (4a). Another way to say this is that you are taking Table (4a) and essentially tacking on the current row's Card_ID value.

To this table you then add a column, Replace, where you try to FIND if the current row's Label_ID is in that same row's Card_ID. If so, return the equivalent Label_Value. If not, return BLANK.

To RETURN, filter out the blanks in Replace and concatenate the values in the Replace column.

Column = 
VAR __Table =
    ADDCOLUMNS(
        GENERATE(
            { [Card_ID] },
            'Table (4a)'
        ),
        "Replace",IF(FIND([Label_ID],[Card_Label_ID],,0)>0,[Label_Value],BLANK())
    )
RETURN
    CONCATENATEX(FILTER(__Table,NOT(ISBLANK([Replace]))),[Replace],", ")