Fuzzy Matching in Power BI

Fuzzy Matching in Power BI, Fuzzy matching allows you to merge tables based on imperfectly matching strings.

This technique is valuable when you need to join tables with slight variations in text data.

Fuzzy Matching in Power BI

The Merge Queries function in Power Query Editor simplifies this process.

Step-by-Step Guide: How to Perform Fuzzy Matching in Power BI

Step 1: Prepare Your Tables

First, let’s assume we have two tables:

  • data1: Contains team names and points scored by basketball players.
  • data2: Contains team names and assists by basketball players.

Step 2: Open Power Query Editor

Navigate to the Home tab in Power BI, and click on the Transform data icon to open the Power Query Editor.

Step 3: Initiate Merge Queries

In the Power Query Editor, go to the Home tab, find the Combine group, and click on Merge Queries.

From the dropdown menu, select Merge Queries as New.

Step 4: Configure Merge Settings

In the new window:

  1. Choose data1 as the first table.
  2. Choose data2 as the second table.
  3. Select Inner as the Join Kind.
  4. Click the Team column headers in both tables to match based on these columns.
  5. Check the box next to Use fuzzy matching.

Step 5: Adjust Similarity Threshold

You can adjust the Similarity threshold value, which ranges from 0 to 1.

A value of 0 matches any strings, while a value of 1 only matches exact strings. The default is 0.8.

Step 6: Perform the Merge

Click OK to execute the inner join based on fuzzy matches in the Team columns.

Step 7: Refine Merged Table

Next, click the left and right arrows on the header of the data2 column.

Check the box next to Assists to include only this column from data2 in the final merged table.

Step 8: Apply Changes

After clicking OK, the Assists column from data2 will appear in the merged table.

Exit the Power Query Editor, and confirm your changes by clicking Yes in the message box.

Step 9: View Final Merged Table

You will now see the new table named Merge1 in the Table view.

Notice how the final merged table matches each string in the Team column of data1 with strings in data2 based on fuzzy matching.

Conclusion

Fuzzy matching in Power BI helps merge tables with slight variations in text data seamlessly.

By following these steps, you can efficiently perform fuzzy matching and enhance your data analysis capabilities in Power BI.

Remember to rename the header data2.Assists to just Assists for clarity if needed.

Power BI Archives ยป FINNSTATS

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

twenty − twelve =