Dismayed is how I felt a few hours ago, when I looked at the 9000 rows of data that I would possibly have to match up manually. Each row contains words that I need to match, but not in always the same format, and certainly not identical to what I needed to match it to!
I’d already spent a few hours cleaning up much of the data, I was not looking forward to more. Then I remembered Fuzzy Matching, because Data Science!
Since I was working in the Power Query editor, I looked up fuzzy matching for Power Query and almost the first result was Power Query and Power BI fuzzy matching.
Unfortunately it turned out that the feature was within Power BI desktop and had not yet graced Excel. Nevertheless, I went into Power BI desktop and had a crack at it. Not much later, I found myself changing the fuzzy matching variables and reviewing the results. First impressions were good, and so much easier than doing it manually.
Then I came to the real obstacle; how to get the fuzzy matched results out of Power BI and into Excel? There is no export to Excel feature obvious to me. Thinking back about it now, surely I could have highlighted that table and copy-pasted it into Excel? A rough approach, but simple enough.
By then it was the end of my work day and so I gave up and came home.
After dinner and a bit more searching, I found the official Microsoft Fuzzy Lookup add-in for Excel, huzzah! I actually ended up having to completely reinstall Microsoft Office because it was hidden somehow, but now I have it working.
It’s quite simple to use, you set up a couple of tables, hit the Fuzzy Lookup button, select your columns and hit Go. Bam, fuzzy matches.
Looking forward to trying it out at work tomorrow.