Power Query Text Replacement

[Eqa33]

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 

[#2]

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

[#3]

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.

[#4]

Sorry typo. "set of letters"

[#5]

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

[#6]

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

[#7 · Accepted solution]

@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]

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.

[#9]

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