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.
Hi All,
I am currently trying to iterate though a table and find and replace matching text in a column. I want/need to do this with DAX as Power Query will return a stack overflow issue due to the amount of rows we have in both tables (Table 1 can have up to 100,000 rows and Table 2 can have around 600 rows).
What I want to do is go through each row in Table 2, find the text of Table 2's first column in Table 1 column, if it is found, then replace it with Table 2's corresponding Second column value.
As a simple example:
Table 1
col1 | col2 | col3 (current) | col4 (expected) |
1 | a | | Green | Yellow | White | 1 | 2 | | Lime | Banana | White | 1 | 2 |
2 | b | | Yellow | White | a | b | | Banana | White | a | b |
3 | c | | Blue | Red | a | a | |Blueberry | apple | a | a |
Table 2
Colour | Replacement |
Green | Lime |
Yellow | Banana |
Red | Apple |
blue | blueberry |
In the tables above, I want to go through each row in Table 1 and then go through each row in Table 2 Colour column, find any matching text in table 1 column 3 and replace with Table 2 Replacement.
I have also used Table 1 col4 as the expected output
Hi @evantse ,
You may try DAX Function REPLACE Function and SUBSTITUTE Function and Power Query M function Text.Replace and Text.ReplaceRange .
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |