Hi,
I am using the list.accumulate function in power query to find and replace a set of around 90 words in one of my data tables in power query. The following youtube tutorial has been my step by step guide to achieve this:
Multiple Find / Replace with List.Accumulate() ~ Power Query - YouTube
The issue, however, is that it takes a lot of time for this query to run, and I need to find a way to optimise it. I would appreciate your help.
Hi @musa.fasih,
It’s not clear to me if you are facing the exact same scenario as in the video where you need to replace multiple words within a string OR if you only need to replace multiple field values in a column?
However the case I think you can try the proposed alternative by Bill Szysz in the comments here.
Also attached a sample for your reference.
Multiple Text Replacements.pbix (15.7 KB)
I hope this is helpful
Hi @Melissa ,
I just tried this solution and i encounter an error whihle working with the lists in my power query. Since I am working on office data, I cant post the actual data. However, I have just created dummy data in which I am facing the exact problem. You will see in PowerBi file in the Power Query, the last step 'Replacements" in the ListReplaceMatchingItems Query, there is an error in the Chnaged Text Expected column. I do not knnow how to overcome this. I have tried tp follow the exact steps as in the article you posted (the steps in power query in the attached excel file that is).
replacementstable.xlsx (15.8 KB)
Sample file.pbix (30.0 KB)
Texttable.xlsx (15.5 KB)
Hi @musa.fasih,
Your CreateListOfLists step returned a list containing errors… I replaced that with Record.ToList(_) and that has resolved the issue.
Note.
“Key Board” is not picked up because you are splitting the text on a space before the replacements so there will never be an occurance of that combination…
Here’s your updated file.
I hope this is helpful.
Thank You, much appreciated!
Hi @musa.fasih, good to see that you are having progress with your inquiry.
Did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Hi @musa.fasih, we’ve noticed that no response has been received from you since Aug 6. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
yes, thanks!
Hi @Melissa,
Your solution did work on my sample Data. However, when I tried it on my company data, there is an error, and I am not sure how to deal with it. In the very last step “Replacements”, when I use text.combine and list.replacematching items, I am receiving the following error:
“Unexpected error: An item with the same key has already been added”
Regards
Musa
Hi @musa.fasih,
I’ve never encountered that error, have you tried googleing it?
I suspect there’s a conflict with a (source) table or column name…
Hi @Melissa,
Yes, I tried googling it, and it did mention of a conflict. However, no conflict seem to exist in my tables or column names. Strange.
Regards
Musa
