cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
davmor Frequent Visitor
Frequent Visitor

Match each item in a list to values in another table

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

 

xxxhappy
yyyweird
zzzfunny

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Match each item in a list to values in another table

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], ";" )

match_each_item_in_a_list_to_values_in_another_table

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

Re: Match each item in a list to values in another table

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], ";" )

match_each_item_in_a_list_to_values_in_another_table

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
davmor Frequent Visitor
Frequent Visitor

Re: Match each item in a list to values in another table

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.

Community Support Team
Community Support Team

Re: Match each item in a list to values in another table

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]), ";")

Match_each_item_in_a_list_to_values_in_another_table2

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.