Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
@tlt,
In Query Editor, we can use Replance Value feature to replce the value we need. However, we can only hard code the "Value to Find" and "Replace With".
To work around this requirement, you could create another table to search value from anther table and return another column value by using LOOKUPVALUE function.
https://msdn.microsoft.com/en-us/library/gg492170.aspx
Regards,
Charlie Liao
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |