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 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 EqaEqa33 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.
- Thanks very much for your help. One more question. I found this while looking for another solution it is exactly what I need, but could you please explain it to me step by step so that I can understand exactly what to do. Thank you.
Riny_van_Eekelen replied to davidmaddock54
Dec 28 2021 08:44 PM
davidmaddock54 The attached workbook contains a working query that does what you asked for. I trust you can get it to work in your own schedule.
It involves adding an Index column from zero. Then a Modulo of 2 on the Index, creating a column with alternating zeroes and ones. Pivot the Modulo column without aggregation of Column1 and do some cleaning up.