Forum Discussion

Copper Contributor

Oct 27, 2022

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 diff...

EXAMPLE.xlsx16 KB

Formulas and Functions

Riny_van_Eekelen
Oct 31, 2022
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

Copper Contributor

Oct 31, 2022

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

Riny_van_Eekelen

Platinum Contributor

Oct 31, 2022

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
Copper Contributor
Oct 31, 2022
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.
- Riny_van_Eekelen
  Platinum Contributor
  Nov 01, 2022
  Eqa33 You found an old thread and I can't really remember what it was about. Better to start a new one and describe your own specific situation and desired result. Much easier to get problems solved that way.