Simple Question: Is there a way to drive a find/replace based on an excel table, with a 'Find' and 'Replace' column?
Longer version / background: For the curious and brave (maybe bored?), below is additional background on the issue I'm trying to resolve. Any input on different approaches would be greatly appreciated. I'm sure there are plenty of cleaner way to resolve the issue that I'm having,
I recently purchased a database with market data for an industry containing rough market share broken down by company and state. There are roughly 167k unique identifiers in the database, many of which appear to be duplicates, e.g. Company A would show up as Company A would show up as Company A LLC, Company A and CompanyA.
I am looking to create a query is easy for someone unfamiliar with PowerQuery to update for duplicate entries as they are discovered over time. While I've identified most of the sizable duplicates already, I am sure that those using the data will continue to find duplicates for some time. Day-to-day, a distributed group of users with varying levels of familiarity with PowerBI and PowerQuery will be using the data. Accordingly, I want an easy way for them to record duplicates as they find them over time.
As suggested by my question above, my brilliant idea is to create a find & replace step in PowerQuery that is driven by a two column Excel table. The table would have two columns: a Find contianing the unique DatabaseKeys that relate to a single entity and then a new consolidated identifier for the Replace, e.g. Find column would have Company A, CompanyA LLC and so on and Replace column would have Customer A for each entry. I'm sure there's some slick way to do this with M and/or a List, but I haven't been able to figure it out on my own.
Any help with short and/or long version would be greatly appreciated
