Forum Discussion
Find and Replace on a Large Scale
Hello,
I am trying to clean a lot of data. In order to do a proper analysis over time I need to identify how many times a company shows up in our data. The trouble is, over the years people have inputted their company name a variety of ways. I thought about building out a conditional column but there are well over 1,000 companies on the lookup table and hundreds of thousands in the data table. I feel like Find and Replace would just take too long. I have an inkling that some code may be needed but I'm just not sure how to go about it. I have put an example below. Thank you for any help you can provide.
List of Companies (Sample Lookup):
| Cheerios |
| Honey Nut Cheerios |
| Frosted Flakes |
| Honey Bunches of Oats |
| Cinnamon Toast Crunch |
| Lucky Charms |
| Froot Loops |
| Frosted Mini Wheats |
| Life |
| Fruity Pebbles |
| Raisin Bran |
| Special K |
| Rice Krispies |
| Special K Red Berries |
| Reese’s Puffs |
| Raisin Bran Crunch |
| Cap’n Crunch |
| Apple Jacks |
| Corn Flakes |
Sample Data (Data Table):
| Cheerios Inc |
| HoneyNut Cheerios |
| Frosted Flakees |
| FrostedFlake |
| Bunches of Oats, Honey |
| Honey Bunches of Oats |
| Toast Crunsh |
| Cinnamon Toast Crunch |
| LuckyCharms |
| Lucky Charms |
| Lucky Charm |
| Froot Loops |
| FrootLoops |
| Mini Wheats |
| Frosted Mini Wheats |
| Life |
| Fruity Pebbles |
| Raisin Bran |
| Special K |
| Rice Krispies |
| Special K Red Berries |
| Reese’s Puffs |
| Raisin Bran Crunch |
| Cap’n Crunch |
| Apple and Jacks |
| Jacks, Apple |
| Apple Jacks |
| Corn Flakess |
| Corn Flakes |
Anonymous Try using a Merge query and use the Fuzzy logic option. If that fails, I did once write a really flexible fuzzy matching DAX measure if you can believe it. Fuzzy - Microsoft Power BI Community It was for a very similar scenario and the Fuzzy matching in PQ just wasn't "tunable" enough.
3 Replies
- Greg_Deckler
Community Champion
Anonymous Try using a Merge query and use the Fuzzy logic option. If that fails, I did once write a really flexible fuzzy matching DAX measure if you can believe it. Fuzzy - Microsoft Power BI Community It was for a very similar scenario and the Fuzzy matching in PQ just wasn't "tunable" enough.
Thank you very much! This was incredibly helpful. Greg_Deckler
v-yingjlCommunity Support
Hi Anonymous ,
It depends on how to want to define the match standards so that you can use fuzzy match in Power Query as Greg mentioned.
You can refer this community blog which introduces fuzzy match in Power Query:
How to fuzzy match the dirty data and horizontal display the corresponding table
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.