Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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".
Solved! Go to 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
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
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
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.
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
User | Count |
---|---|
97 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |