BULK Replace Values in Power BI / Power Query

Downloading the workbook here:
www.excelguru....
Check out a better way to do it here:
www.poweredsol...
Get a free copy of my Power Query eBook here:
www.powerquery...

[@ziggle314]

This video solved a real issue that I needed to deal with today. Thank you!

[@vishaljhaveri7565]

Insightful method to achieve the same requirement. Thank you :)

[@jdodson40]

Great video. Need some additional help. I have a PowerBi Data model and I need to convert 20-30 columns of data within a table (approx: 80columns x 500 rows). Each column's data needs to be converted using a unique list of data. So I have 20 - 30 unique lists of data. How do I use this bulk find and replace method to iterate through each column of unique data? Any help you provide would be very much appreciated.

[@ThePowerUser]

hey! here's a more thorough explanation of the topic: www.poweredsolutions.co/2019/07/01/recursive-functions-in-power-bi-power-query/

[@jdodson40]

@@ThePowerUser Thank you very much. I am going through all the topics now and am excited to solve this problem

[@BillSzysz1]

What a wonderful piece of work !!! But i think that, using recursive function, this can be done much faster and ( i think ) the difference will be grow when table will increase Give me a few minutes (maybe half an hour) and I will check my idea.:-) Thanks Miguel !!!

[@ThePowerUser]

glad to see you around! I'm trying to reactivate my channel and trying to hit the more advanced scenarios with Power Query and Power BI Overall. I feel like it's time :) You should record some videos too!

[@jazzista1967]

Thanks Miguel for posting and explaining this elegant solution vs the clunky way to do it in excel

[@ThePowerUser]

gracias! :)

[@chakrabmonoj]

Hey - this is a great video indeed and I am finding this most effective amongst the others available on the net. The problem with the code is however that it is not replacing the text strings which have multiple strings, sometimes separated by space and sometimes separated by comma. Is there a workaround for that?

[@XenArena]

Hello, How did you create the function?

[@ThePowerUser]

hey! Had to read a lot of the documentation and even request info and help from Microsoft to understand the language better. I tried to capture my knowledge in this video so I could pass it on, but beyond that its just reading the function reference.

[@axetro1578]

Hello, thank you for this wonderful trick! But i wonder, is there a way to do something like this but for multiple columns instead of just 1 column?

[@ThePowerUser]

I believe you could use something like Table.TransformColumns and apply the function to multiple columns that way

[@sushilchaudhari214]

Thanks for the video. I want to replace text within parenthesis with none and i have multiple instances of parenthesis in one cell. Is there any way we can do this thing? Thank you.

[@ThePowerUser]

probably best to split by the start and end of the parenthesis and then merge the contents back than trying to replace

[@stevemoosemusic]

Is this a find/replace for exact match or is it repetitive - i.e. if I did this with part numbers that had multiple variations - "Product1" and "Product1B" and Find/Replace "Product1" to be "Apple" would I get "Apple" and "Product1B" or would I get "Apple" and "AppleB"

[@ThePowerUser]

hey! you could modify the Replace function to either be an exact match or just a substring. The function that drives the magic is this one: docs.microsoft.com/en-us/powerquery-m/table-replacevalue and the replacer function that would drive an exact match would be docs.microsoft.com/en-us/powerquery-m/replacer-replacevalue Hope this helps!

[@IrshadKhan-nd2rg]

@@ThePowerUser what if I want to use Product * replace with Product... as I don't know what is next to Product...

[@CarlosestebanVargasMoreno]

Excelente, saludos desde Colombia, el mejor pais del mundo.

[@juandiego2393]

jajajaja

[@jimmyni1983]

Hi, how are you? A HUGE HUGE THANKS A LOT @The Power User bringing this amazing function that saves me right now! I wish to learn really advanced M language! Please keep up your great great job! Thanks again and if you believe in God... God bless you!

[@edsonms12]

Thank you for the shared. Solved a real issue!

[@777tmack]

Another time saver! Thanks, Miguel!

[@gtaranti]

Hey thank you. But how do we address for null values of the table? The function returns Error if the table row is null.

[@ThePowerUser]

similar to how you'd do it in Excel with IFERROR, you can use the try keyword inside of Power Query. So you'd wrap your function around a try statement with something like: try thisFunction(parameter) otherwise null which in simpler terms it translates to, if that operation brings an error, then just output a null value. There are other ways to bypass null values, but they might be too complex or troublesome

[@jaszkari]

Excellent stuff! I could re-use the code in my Power BI report. Thanks for sharing! :)

[@MichaelJamesActually]

Hi from 2.5 years in the future. Thank you for this video. I was able to invoke the fxn ok. I am replacing a string in a column of 500k text values. The whole table is ~180 MB. However, upon applying, the size of the table keeps increasing. It's currently up to 750 mb and rising. I may be having some memory leak or other issue not related to the function. I wanted to check if you had seen anything like this. Thanks again for your help!

[@ThePowerUser]

Michael James check out this article: www.poweredsolutions.co/2019/07/01/recursive-functions-in-power-bi-power-query/

[@saberjaztx9416]

Hi do you have a video where I can get the if error function in power query excel?

[@ThePowerUser]

i have this article www.poweredsolutions.co/2019/06/18/error-handling-iferror-in-power-bi-power-query/

[@LongZhiZiLaoShi]

Great function. Is there a way to do the bulk replace to match entire cell instead of the text inside the cell. The M code looks the same for both options. For example: I have United State and United State of America. I want United State of America for everything. If I use this function, I end with United State of America and United State of America of America. Thank you

[@ThePowerUser]

Hey! you're correct. The pattern described in this video is to look for substrings within a value and replace those, but if you want to look for the exact match of the contents of a full string then you'll need to use a function that can take the whole string. A function like = Table.ReplaceValue that uses Replacer.ReplaceValue would be the best place to start

[@kurtturner5120]

Is the code the same for replacing number with different numbers? Thanks!

[@ThePowerUser]

This one uses the replacervalue for text, but you could customize the code to use the mods to only search and replace for numbers

[@zenaura-tranquilmusic4054]

Hi, I want to use this to replace number values in my data. I have followed all the steps however I am a little stuck. In the function you have used Text.Replace. My columns are a whole number so which function should I used instead of this text based function. I have also changed the first line to make x as number. Thanks

[@ThePowerUser]

hey! if you're trying to replace values, then it's probably a better idea to do a merge operation instead. It would be A LOT faster. Let's say that you want to replace #114 for #20, then you could do a merge operation to create a new column that would have the "translation". It wouldn't exactly be a replacement, but it would get the job WAY faster.

[@JoshuaDHarvey]

Seriously man? You published this in 2017 using Camtasia and you couldn't zoom in on that tiny text so we could see each line and not only that the PowerQuery text editor is complete crap. Thanks man, thanks...

[@ThePowerUser]

hey! thanks for the feedback. Yes - this was spot on 3 years ago. You should check out the blog post that I wrote on a revised version of this video a few months ago: www.poweredsolutions.co/2019/07/01/recursive-functions-in-power-bi-power-query/ thx!

[@JoshuaDHarvey]

@@ThePowerUser Ok will do! Thanks for the reply, sorry I was being a prick

[@accfinmath3569]

@The Power User this is awesome. Thank you.

[@eqweqwev2572]

Excellent stuff!

[@ThePowerUser]

thank you!

[@RicardoLazzaridaRosa]

Displays this error. What would it be? Expression.Error: Failed to convert the "[Function]" value to the Function type. Details: Value = [Function] Type = [Type]

[@ThePowerUser]

hard to say. I'd recommend posting your full scenario and code on the official Power Query forum here: social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

[@ΠαναγιώτηςΧατζηαντωνίου-ι7θ]

Thank you so much...

[@powerdata6063]

Great usefull video: But I have a problem with a Token Comma expected and i don´t know were? (x as text) as text => let MaxIterations = Table.RowCount(TblReplace) , Iterations = List.Generate( () => [Result = Text.Replace(x, ReplaceTable[OldText][0], ReplaceTable[NewText][0], Counter = [0], each [Counter] < MaxIterations, each [Result] = Text.Replace([Result], ReplaceTable[OldText]{Counter}, ReplaceTable[NewText]{Counter}), Counter = [Counter]+1, each [Result]), Output = Iterations{MaxIterations-1} in Output Can somebody help with the script?

[@ThePowerUser]

Hey! The KZbin Comments is not a friendly interface to help you out :( - I've been trying to answer your question but I always have some issue posting the response. In short, there are some missing [ ] in the 3rd parameter of your list.generate function - that's the token part, but your function might require some work. I'd highly recommend that you post your question on the official Microsoft Power Query forum: social.technet.microsoft.com/Forums/en-US/home?forum=powerquery WAY easier to post a response there and you'll get help not only from me but also from a lot of other community members and the actual Power Query team

[@AlejandroBedoya00727]

Hello, thanks for the information, your video is very interesting. On the other hand, could you help me please, in a registry I have the following text string C - B - A and I want to order it like this A - B - C using Power Query.

[@ThePowerUser]

Hey! that's a tough one! You'd need to create your own custom function for the job and I'm assuming that this is a simplified version of your full scenario, so that might be something else. I highly encourage you to post your question on the official Power Query forum here: social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

[@thiagofraletti4220]

thank you!

[@miltinhocamo3754]

Hola Miguel... gracias por el truco. ¿Podrías explicar mejor la creación de la función, es decir, un paso a paso de la forma en que se creó?.

[@ThePowerUser]

Hola! pronto voy a subir otro vídeo con otro ejemplo un poco más sencillo sobre List.Generate y cómo funciona. Saludos!

[@ThePowerUser]

te debo la versión en español, pero acá te dejo la versión en inglés kzbin.info/www/bejne/nJW4k4ilnJWBerM Espero en un futuro muy cercano abrir otro canal dedicado a contenido sólo en español. Espero el vídeo sea de tu agrado y gracias por tu tiempo! Miguel

[@miltinhocamo3754]

Super... gracias Miguel.... Espero el canal en Español con ansias.

[@joefromdc]

great video, pls zoom in next time

[@ThePowerUser]

hey! sorry about that. Count on it for the next video.

[@ThePowerUser]

Hey! let me know what you think of this new vid: kzbin.info/www/bejne/nJW4k4ilnJWBerM Took your feedback into account :)