Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |