Replace or remove multiple characters using List.Accumulate in Power BI (Query editor)

Here I will show you how to replace or remove multiple characters using List.Accumulate function together with Table.ReplaceValue function inside the query editor in Power BI.
For consulting, solutions or training needs, Data Zoe is at your disposal.
Linkedin: / datazoe

[@snipelite94]

Other content creators have tried to explain this but it takes multiple viewings Your lecture is wonderfully explained and substituting "state" and "current" helped immensely to see into the workings of this engine Best explanation yet, sir 😎

[@ulissesdietrich2384]

Thank you Snip Elite!! 😁

[@SndfOmar]

Great example! Easy & to the point. Subscribed.

[@kennethpaige7036]

That is one of the best tutorials I have seen in a very long time. I have been struggling with List.Accumulate for several days since none of the previous tutorials broke down the mechanics of how the accumulation works. You made it obvious, and I very much appreciate the help.

[@ulissesdietrich2384]

Thanks for your feedback Kenneth!! 😁

[@dataisfun4964]

Beautiful, you just got a new subscriber.

[@debrasimpson4986]

This is almost precisely what I was looking for, but for open parenthesis "(" I want it removed or replaced with null and for closed parenthesis with a space ") " I want it replaced with a dash "-". Since this demo is a 2-step process I'm going to stick with the 2-step method for the meantime, but I feel I could really use what you showed in future transformations.

[@ramonillarramendi3191]

Thank you very much! Excellent explanation.

[@AniManuSCh]

Awesome

[@zoehealing]

Wow!! Perfect!

[@datazoe3283]

Thank you!

[@AniManuSCh]

Is there a way to combine data just like this but when the data comes from web all packed in one single cell? I mean on the web look like a table, but then you find that it is all listed on a single column and row, so I have been struggling quite a bit now trying to figure out a way to transform it on to a table. Your video is the greatest and closest thing I have found so far, Thank you very much!!!

[@AniManuSCh]

by the way here is the link for the table: gee.bccr.fi.cr/indicadoreseconomicos/Cuadros/frmVerCatCuadro.aspx?idioma=1&CodCuadro=%20400

[@shetzel9722]

Great explanation, Thank you.

[@josh_excel]

I took your solution and combined it with mine to give the following, where T1 = word table and T2 = Find/Replace Table = List.Accumulate({0..Table.RowCount(T2)-1},T1,(table, list)=> Table.TransformColumns(table, {}, each Replacer.ReplaceText(_,T2[Old]{list},T2[New]{list})))

[@josh_excel]

let Source = null, T1 = #table(type table [Col1=Text.Type,Col2=Text.Type,Col3=Text.Type],{{"the", "quick", "brown"}, {"fox", "jumps", "over"}, {"the", "lazy", "dog"}}), T2 = #table(type table[Old = Text.Type, New = Text.Type],{{"a", "ȁ"}, {"b", "ƀ"}, {"c", "ç"}, {"d", "đ"}, {"e", "é"}, {"f", "ƒ"}, {"g", "ĝ"}, {"h", "ĥ"}, {"i", "î"}, {"j", "ĵ"}, {"k", "ķ"}, {"l", "ĺ"}, {"m", "ɱ"}, {"n", "ŋ"}, {"o", "ø"}, {"p", "þ"}, {"q", "ɋ"}, {"r", "ř"}, {"s", "ş"}, {"t", "ţ"}, {"u", "û"}, {"v", "ʋ"}, {"w", "ŵ"}, {"x", "ẋ"}, {"y", "ÿ"}, {"z", "ƶ"}}), Custom2 = List.Accumulate({0..Table.RowCount(T2)-1},T1,(table, list)=> Table.TransformColumns(table, {}, each Replacer.ReplaceText(_,T2[Old]{list},T2[New]{list}))) in Custom2

[@rafaelbarbosa169]

amazing!!!

[@goodheart2817]

Hello...Thank you for your video. I have a BIG PROBLEM. I want to input A's into several columns on the same row which has number 16. How do i do that please?

[@muftkuseng5924]

How can i use this if i want to do this on a about 30.000 rows and a list of 200 words. its loading more than 5gb in it because it is probably pulling the list multiple times into the system. how can i increase the performance here with list or table.buffer?

[@josh_excel]

kzbin.info/www/bejne/jIipinhjebSsotE

[@leonrobinson2053]

I thought I was being a thicko and i probably am but this works perfectly. I had a column with mainly numbers (like a reference) but some obs had letters or double letters my Text.ToLIst was ("ABCDEFGHIJKLMNOPQRSTUVWXYZ") and it got rid of the individual letters, even if they were doubled up and not in order so for example 1078SB56320, was 107856320 afterwards. Perfect tutorial.