Multiple Find / Replace with List.Accumulate() ~ Power Query
Vložit
- čas přidán 13. 05. 2020
- In this video, let's talk about List.Accumulate() Power Query function. This versatile function can apply an operation on all items in a list and return one final value. We use this little gem to perform multiple find / replace in a piece of text.
This technique is useful for replacing keywords or changing text en masse.
For full discussion, example file and more, please visit:
chandoo.org/wp/multiple-find-...
#PowerQuery #List.Accumulate #KeywordReplacements #FindReplaceAllItemsExcel - Věda a technologie
I just stumbled upon this at the perfect time, I needed a solution worked out in two days and this was it, thank you so much!
Less than a month ago I was looking to do something similar. In my situation I had headers like column01, column02....column 21. I thought there must be a better way than manually retyping each column name.
Awesome work. You made my life damn easy.
Thanks a TON
Chandoo - great video as always! The fix for the bug that repeats the first part of an M code function (the part before the period) is to type the function WITHOUT the period. Note, that the bug (in the latest beta version of 365) is only in the dialog box when using the tools in the ribbon. The Advanced Editor no longer has the bug.
So, in the Add Custom Column dialog box if you type "listacc" and hit [TAB], "List.Accumulate" will appear. If you had typed "list.acc", you'd get "listList.Accumulate".
Hopefully they'll fix the bug in the dialog box soon!
Good point Jerry. Another option I found is instead of pressing TAB if I press ENTER the autosuggest seems to do what it should.
hi chandoo, i have very big messy data of suppliers name, the problem is i can find 1 suppliers name wrote in 15 different way, I would like to uniform the name so that i can use pivot table to summarize each spend we made to these suppliers, do you know how to do it in power query?
This helped me today, thanks!
Thanks Chandoo.. interesting challenge and technique for solving it. Will have to study further. Thanks for sharing it! Thumbs up!!
Thanks Wayne..
First of all, thanks for the tutorial. It's very useful. However, I have 1 question, I am trying to replace 1800 over rows to standardised naming convention but it takes so long. Is there a way to speed it up? Thanks !
Do you find other solution brother? I tried on my 5000+ rows and face the same problem. Thanks
Hi @Chandoo
Thanks for the video. This is very helpful.
I have to perform this operation on a big table (Having 50 columns). So Add column is difficult.
Is there a way to implement this for a table?
Haven’t tried this yet, but it will replace part words, won’t it? Could produce issues at times. Also would be case sensitive for text.
Example:
Find six Replace four would change sixteen to fourteen but also sixty to fourty... and would fail if there is capitalisation : Sixteen would remain unchanged.
That said, I foresee using this as a powerful data cleansing trick.
I am trying to work out how this could work if you had multiple columns as conditions. Would I have to use a helper column where concatenate the columns?
Hey, Chandoo. Great watching this tutorial but I found using Reduce and lambda function to handle this issue as you've shown a tutorial about using this technique more than power query. Which one you think more handy to use?
I don't understand the relevance of this....
List.Numbers(0, Table.RowCount(replacements))
is this just a dummy List, to satisfy the parameter?
Outstanding as wellas mindblowing techniques for find as wellas repalce fora multile times.
Thank you for this, you gave a really nice explanation. I'd also appreciate tips or work arounds to speed this up :)
Hi Chandoo, thanks that was helpful, I looking for formula where if it finds the text in a cell, that cell should get replaced with another text or value or we can perform this in new column.. can you please help with the formula
absolutely amazing, list replaced a list!
Glad you liked it!
Wooow, really useful. I used List.Generate but this one is simpler.
Thanks
Glad it was helpful!
That was insane ! thanks sir !
🤯
Uff, you made my life simple. Thanks Chandoo Ji
Hi Chandoo, how to do this replace only if two columns from both tables match
Wonderful explanation! Is there a way to combine the functionality of list.accumulate with splitting text over several delimiters? I've been working for several hours on this but I'm coming up empty. Any help would be greatly appreciated.
Thanks R L. If your delimiters are single chars, you can use = Text.SplitAny()
For example, = Text.SplitAny("something or other, but not both. Mr", " ,.") will split the text for any of the space, comma or period delimiters
PERFECT EXPLANATION of List.Accumulate !!! Could you, please, make a video about List.Generate?
Thanks for the love. I will add a video on List.Generate in a while. I am looking for a practical application for this. If you have any suggestions, please comment.
@@chandoo_ If I knew List.Generate:)
Chandoo - This is great. However, when I try this myself, it takes a LONG time to load. My data sets are small to start (10 rows and 30 columns in the data, and 10 rows of special character to hexidecimal code). Any thoughts on what could be happening? Its getting up to the 9 GB size and takes over a day to load.
Do you find the sollutions brother? I tried on my 5000+ rows and face the same problem
Hai sir, i would like to know
From a particular list if there is a value is null, then pick the value from the next column how can I exicute this through power query...
Thank you Sir, This is very useful.
You are welcome
This is a wonderful lesson Chandoo...I have a long list of cities, which will require this kind of replacement. Is there a way the code can run in a case-agnostic way? I have run your code on my table, but its returning the exact same names and not replacing anything really.
Could this be happening because the target table has the city names in uppercase and the replacement table I have made, by mistake I got the city names in lowercase?
Any help much appreciated.
Power Query is (strangely and annoyingly) case sensitive. I would just lower case everything before doing any operations like this.
@@chandoo_ I did that - changed everything to one uniform case...but there are two strange problems :
1. If the text to replace has multiple words - like "Navi Mumbai", it is not replacing &
2. In some cases, all instances are not getting replaced - e.g. out of 6 instances of "Howrah", 5 got replaced with the intended replacement of "Kolkata", but one instance did not.
Can you throw some light on that?
Thanks
Thanks you for this. You showed that this list can work for text. What about numbers? Is it possible to replace numbers using this function?
You can do it with numbers too. Power Query has specific data types for everything. So either convert numbers to text (Text.From() should work) or change everything to numbers.
Amazing! Than you, Chandoo!
BTW, what fonts you used for the slide @ 4:00.
You use great presentation slides, Chandoo.
How can we change the whole value of the cell and not only a part of the text ?
(I'm trying to use the Table.ReplaceValue instead of the text.replace but it gives me
an error).
And how can we do the replacements in the original column without creating a new one ?
Thanks you
Hi Yassine... just use Replace values button in the Home ribbon of Power Query editor for this.
@@chandoo_ Hello, what i mean is how to do it in the context of your formula, because i'm also usng another table as a reference.
You are a genius!
Thanks Eric.. I am glad you found this useful :)
i tried this in my report in which Style number should be replaced by Style group (ex AM1234 as A/1234. This is for 1000 of rows with multiple such style group. Getting error.
awesome. Thank you so much
You're very welcome!
Excellent! Exactly what I needed. New follower here. :). Thanks!!
Welcome aboard Patrick. :)
Can this be replicated for multiple columns?
This is what I wanted
Dear Mr Chandoo What is the limit of maximum words we can replace
I used your technique to change header names coming from multiple excel files. But ALL List.Accumulate did was to capitalize each word in the header.
Any reason why?
It depends on what you are using to ACCUMULATE. See the video again and examine description links as this is a tricky concept.
Nice and Very informative Video.
Thanks for liking
How can I tweak this to show a column of the keywords found instead of replacing the text?
Good idea. Why not try playing around in PQ and see which M functions can let you do that.
@@chandoo_ LOL, I cheated and used my replace values encapsulted in delimiters and then extracted between delimiters to create a bucket. :)
superb...thanks
WOW. GBU. Thanks for Accumulate fun.
Thank you Haider :)
Please do a video showing how to do dynamic replacement using a list of columns, also at the same time, to make the replacement conditional.
Sure. I will think about this.
Hi, Thanks for the video... I was looking for something like - I want to replace "Apple lghlkdshlhdshglsh" with "Apple" so i have used * sign in excel vba to replace unknow things after Apple... but power query gave me error using the same thing in power query.
Greetings from Riyadh, Saudi Arabia.
I am working in Retail Industry. On an average we have 500 K invoices in a month and mostly customers are paying by Card. We have over 125 stores spreading across in 32 cities.
Unfortunately our banker is not giving POS # (Point of Sales machine number) in a separate column. It is part of Transaction description. More over POS Machine # is not fixed in the transaction description.
We have POS Machine # and Store ID for all 125 stores (POS Master File). Is there any possibility to get the following solution from Power Query:
---> Identify POS Machine in the Transaction description table and add Store ID in a separate column based on the POS Master file. I tried List.Accumulate function but it is replacing POS # with StoreID in the same column along with other text. But I need store id exclusively in different column. Looking forward your solution brother.
This is very very good, but when we want to replace values, we want to replace them in the existing column, not create a new column. Since Table.ReplaceValue is a very different kind of function than Table.AddColumn, I wonder how you could implement this solution to change the values without creating a new column.
There is no cost to creating a new columns in PQ. So instead of figuring something else out, why not add the column with the replacement logic and then remove the original column?
Great
💯👍
I posted a companion article on this. Check it out for more + sample file here: chandoo.org/wp/multiple-find-replace-list-accumulate/
Thanks a lot. I already used it at work. A small error has crept in ... After Table.RowCount -1 is unnecessary. Skip the last line.
Yes, you are right. Yeah, it was an error that I had too. If you see the video at end, I realize that mistake and fix it.
I'm inattentive ... I'm sorry. You can also create a list like you at the beginning of the movie. {0 .. Table.Row.Count (tablename) -1}
Little difficult but thanks for helping
No worries!
Thank sir. This video is very useful.
Sir please explain this video in HINDI.
Hi Prashant.... Thanks. I am glad you enjoyed it. I am not proficient in Hindi. But I will try add a Hindi or Telugu video once in a while. As Excel interface and resources are all in English, it is a lot easier to explain in that. As they say... koshish karoonga… :)
Why not use List.Generate, I don't see the point in having a state.
Sure. If that method works for you, use it.
Don’t type dot when searching for the function. There’s no bug
In my book, it is a bug. The Power Query intellisense has been buggy for years and just shows developers are not thinking thru real-world scenarios.
Not explained in effective way
List.Accumulate is one of the trickiest to understand and explain. If you mastered it, feel free to make a video or blog article and share it with us.