Forum Discussion
Mass search and replace
Hi,
I have a table with millions of rows of products from all kinds of customers and the sales numbers of these products.
Products table
Product name | Sales |
Coca Cola (1 liter) | 10 |
Coca Cola in plastic bottle - 70cl | 10 |
Coca Cola 375ml can | 10 |
Pepsi 6x1 litre pack | 10 |
Pepsi Max | 5cl | 10 |
As you can see this will require lots of cleaning to get a good understanding of the total sales by product or by brand. Every customer fills out their product name in a different way. Unfortunately just using functions like extract before / after delimiter or range don’t work, as there is never a fixed range or delimiter. So I reckon the easiest way to clean it up is a mass search and replace.
Replace table
Search input | Replace output |
(1 liter) | 1000 ml |
in plastic bottle - 70cl | 700 ml |
375ml can | 375 ml |
6x1 litre pack | 6000 ml |
| 5cl | 50 ml |
To do that I created a search and replace table. It will probably eventually contain close to a 1,000 rows.
I’m looking for a function in Powerquery that is going to search each row in my PRODUCT NAME column for all the rows in the Search input column and do a replace where possible. How do I do something like that?
I understand that will have quite an effect on the refresh speed, so any other ideas on how to do this are more than welcome.
Regards
Bas
3 Replies
- ibarrau
Super User
Hi. I don't think this will be solved automatically, you need to do some manual work. If you are just going to use Power Bi, you can try Fuzzy Logic Merge. It takes a long time, but if it doesn't take more than the timeout you will be ok.
In order to do this, first create a column in excel file with the expected results, for example: "Coca Cola, Pepsi, etc". Then load this on power bi and merge both query together. When the merge options are diplayed, you can check the fuzzy merge and play with parameters to get different probability of precision.
Regards, hope this helps
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your helpibarrau ,
I've tried fuzzy merge, but that unfortunately makes refresh going so slow that it would take days to finish, even when changing the threshold to the minimum.
I then stumbled on this Youtube video https://www.youtube.com/watch?v=MLrRlPh_ZFQ that explains how I can do multiple search and replacements using a separate query. I tried it with a table of about 30 search and replacement rows. I went a bit faster, but again as with 30 searches it already took so much time, with 10 times the amount of search and replacements it would take forever.
Lastly, what I've now done is simply go for a manual search and replace in Powerquery. It now holds about 80 of them, and though it's not ideal it does seem to be the fastest solution.
So I guess you are right in saying it cannot be solved automatically just like that!
Thank you for your help though.
Regards
Bas
- ibarrau
Yes. Power Bi is not the best option for this problem. You could probably try some machine learning classifier or something like that to find the best product name for each one.
Regards,
If you think the last post I wrote help you, then please consider Accept it as the solution to help the other members find it more quickly.
