Power Query: Bulk Replace Values from a table in Excel & Power BI

Replace Values step is used so often in Power Query, that it can lead to lots of query steps and slower load times. If you want to achieve the same result with fewer steps, I show you how to do that in two ways to reuse in either Excel or Power BI:
1. Bulk replace when you want to change the entire cell contents
2. Bulk replace when you want to change part of the cell contents from a table.

[wayneedmondson]

Hi David. Interesting approach! Thanks for sharing. Thumbs up!! PS - Chandoo has a good method for bulk Find and Replace using List.Accumulate. That's my goto option when needed.

[johnwatson]

Thanks, first video I watched authored by you. You have a new subscriber now! Thanks!

[jerrydellasala]

That problem with the PQ Editor is really annoying! However, in case you or anyone following the channel haven't learned how to prevent it, it's really simple. DON'T TYPE THE PERIOD! Instead of typing "Text.Split" type "TextSplit" and hit [Tab] when the function appears at the top of the list. Warning - it takes time to get used to NOT typing the period! Cheers.

[pavankumar-hwzb]

Make more videos on power query tricks like this

[donovannewton]

Ok so this is brilliant.

Is there a way to repeat that for multiple columns with the same “Words”. As an example, say you had the number “1” in multiple columns to an “X” of columns?

[kumarthombre]

Thanks for sharing. It’s really informative

[brightonalbert]

thank you so much no need for functions

[yaravanleeuwen]

Thanks David for making this video.
Very happy as it is exactly what I was looking for a long time.
Just when making the table...how can I adjust values meanwhile?
I figured I don't get a lot second chances or redo's in power Query.
Do you know how to handle this?
cheers, Yara

[victorvaldivieso]

Great video!! Do you know if this works for a Dataflow? I followed your steps in PowerBI Desktop and it worked perfectly however I'm doing the same steps in PowerBI online (to create a Dataflow) and it gives me the following error "Expression.Error: We cannot convert the value "[List]" to type List.DetailsValue = [List]". I'm really struggling with this, hopefully someone can help!

[SianDoherty]

Thanks for a helpful video David. Starting at 11:27(ish), you mentioned that this technique doesn't work if you try to replace multiple words to a single word or to multiple words, can you advise the best technique to achieve multiple word replacement to a single word or even multiple word replacement to multiple words without replacing the whole cell?

[elisabethmcdonald]

Is it possible to do the first method to two columns?

[felipeleite]

Thanks Davi! Incredible. How would you make it case-insensitive?

[leebecker]

Thanks David, great video. How do the two methods (replacing with lots of steps vs replacing using merged table) compare in terms of efficiency? For example do you think it's worth using a table to merge if there are more than x number of replaces?

[chakrabmonoj]

very helpful lesson David -could you point me to a source to find the code for solving for multiple text strings into single text string?

[carlosraziel]

Hi, good morning, thank you very much for sharing. I am a new user and with your great explanation, even I understood. Can I make a quick consultation? About your own example, how could I replace a text that I know how it starts and I know it ends with a space, the length of the text can be variable.

Saludos desde Cancun :)