Forum Discussion
Power Query Text Replacement
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.
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.
- Thanks for your reply. You are correct with your understanding. Are you saying that I need to do a v-lookup outside power query? I receive this type of report weekly. How do I get automate the process through power query if this being done outside PQ?
Thanks again Eqa
- Sorry typo. "set of letters"
