Merging Queries in Power BI: Exact Match and Fuzzy Match Unified

Learn how to combine queries in Power BI with an `exact match` on one key and a `fuzzy match` on another for comprehensive data analysis. --- This video is based on the question https://stackoverflow.com/q/72665739/ asked by the user 'belle' ( https://stackoverflow.com/u/18085436/ ) and on the answer https://stackoverflow.com/a/73028380/ provided by the user 'belle' ( https://stackoverflow.com/u/18085436/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions. Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: When merging two queries in Power BI, can I exact match on one key and fuzzy match on a second key? Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l... The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license. If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com. --- Merging Queries in Power BI: Exact Match and Fuzzy Match Unified When working with data in Power BI, you may find yourself needing to merge two tables where one key requires an exact match while another key necessitates a fuzzy match. This scenario can be a bit tricky but is certainly possible with the right approach. In this guide, we’ll guide you through a solution using Python in Power BI to achieve this complex merging. The Problem Let's consider a scenario with two tables: Table 1 has a column named "Key" (which is an exact match), along with a "Name1" column. Table 2 also has a "Key" column, but this column is not unique; it can have multiple names ("Name2") associated with each key. Example of the Tables Table 1 KeyName1info_a1Michaela2RobertbTable 2 KeyName2info_b1Mikeaa1Andreacc2Robbiebb2MichelleddYour goal is to merge these two tables so that: An exact match occurs on the "Key" column. A fuzzy match is applied to the "Name" columns, matching "Name1" in Table 1 to "Name2" in Table 2. The Solution To achieve the desired result, we can utilize a Python script within Power BI. Here are the step-by-step instructions to implement the solution effectively. Step 1: Merge Table on Key Column Start by merging your two tables using the "Key" column with an exact match: Use Merge Queries in Power BI to join Table 1 and Table 2 on the "Key". Step 2: Set Up Your Python Script Here’s the Python script you need to create the fuzzy match on the "Name" columns: [[See Video to Reveal this Text or Code Snippet]] Step 3: Filter the Results After running the script, you will need to filter the dataset to only include the best matches: Add a filter where MatchRank equals 1, ensuring you get the top match based on the fuzzy score. Resulting Table The expected result after applying the above steps would look like this: KeyName1Name2info_ainfo_b1MichaelMikeaaa2RobertRobbiebbbConclusion Merging queries with both exact and fuzzy matching in Power BI can be easily accomplished with the help of Python scripts. By following the steps outlined in this post, users can effectively analyze data even when faced with non-unique identifiers. In summary: Start with a straightforward exact merge. Use Python for the fuzzy matching logic. Filter to keep the best results. Now you can tackle more complex data analysis tasks utilizing both exact and fuzzy match methodologies in your Power BI reports!