Forum Discussion
Replace values from one table to another
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
1 Reply
v-xicaiCommunity Support
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.