Forum Discussion
Solved
Power Query Text Replacement
Hi,
I have a column in power query made up of 2 words as per my example. Once I split the column I want to replace the first word with another word, but as every number changes in the example a different word is to be used as a replacement. So; I need to split the word below at the point of XYZ
| CZ-C202-XYZ |
the 2 means CZ-C202 should be replaced with ABC
CZ-C301-TYU
the 3 here means CZ-C301 should be replaced with DEF.
How do I do this?
Hope this makes sense. Thank you
Eqa33 No need to use VLOOKUP at all. Within PQ you merge information from different tables, resembling a VLOOKUP function in Excel. You connect to both tables. The data table that changes every week and the static table with the numbers and their replacement codes. Just make sure that the weekly file has the same name every week and is located in the same folder. Then just refresh the query and all gets updated.
8 Replies
Eqa33 Start by creating a table that contains two columns. One column with the number and another with its replacement work.
Then, after splitting the string at the last hyphen, extract the last number from the first part. Easiest would be to merge the larger table with the table I mentioned above based on the number.
you'll find a working example in the attached file.
- Thanks for replying. I don't understand how this will automate a process of recognising a number and replace it with a given ste of letters? Could you please explain in more detail. Thank you.
Eqa33 Perhaps I misunderstood but you have to let Excel know which numbers should be replaced with what. That's where the extra table comes in. All the query does is connect to the data. Split off the last part en extract the number at the end of the first part. Then it looks up that number in the extra table to return the characters that should replace the first part of the text string.
Once set-up, change the source data, refresh the query and all will be done automatically.
