Match each item in a list to values in another table

[davmor]

I have a column where the values are a list of items concatenated with delimiters (e.g. "xxx;yyy;zzzz"). I want to be able to lookup the value of each from another table

xxx	happy
yyy	weird
zzz	funny

and then concatenated so that in this case it would return "happy;weird,funny".

[v-jiascu-msft · Accepted solution]

Hi @davmor,

Try this formula, please. Please also check out the demo in the attachment.

Column =
VAR temp =
    CALCULATETABLE (
        VALUES ( Table2[Col2] ),
        FILTER ( 'Table2', SEARCH ( 'Table2'[Col1], [Column1], 1, 0 ) > 0 )
    )
RETURN
    CONCATENATEX ( temp, [Col2], ";" )

Best Regards,
Dale

[Anonymous]

@v-jiascu-msft 

I like this solution but for me, there is a bigger issue where I have:

a list of: spa-10, spa-100, spa-200 etc

and a table of all spa-numbers and descriptions

but when I try to match the spa-numbers and output descriptions to these list, this function returns:

spa-17, spa-173, spa-1739

even if only spa-1739 exist in the list.

is there a way to fix it? i guess the table is reading the "spa-1739" in the list and read it only until spa-17** and says its a match pulling extra ones to it

[davmor]

Thank you for the quick reply. It worked perfectly.

I would need a slight modification in case there is no match so that it returns the original string. For example, if we start with xxx;aaa;zzz it should return happy;aaa;funny.

[v-jiascu-msft]

Hi @davmor,

I'm afraid it's hard to do it with DAX. Please try it in the Query Editor.

Text.AfterDelimiter(
  List.Accumulate(Text.Split([Column1], ";"), "" , (step, base) => 
    if Table.IsEmpty(Table.SelectRows(Table2, each [Col1] = base)) 
    then step & ";" & base 
    else  step & ";" & Table.SelectRows(Table2, each [Col1] = base){0}[Col2]), ";")

Best Regards,
Dale