Table of Contents
Fuzzy matching in Power BI is a powerful tool that allows users to compare and match strings of text that are similar but not exact. This can be particularly useful in situations where data may contain spelling errors, abbreviations, or variations in formatting. To perform fuzzy matching in Power BI, users can use the “Fuzzy Merge” feature, which allows them to select one or more columns and specify a matching threshold. This will then generate a new column with matched values based on the specified criteria. For example, if one column contains “John Smith” and another contains “Jon Smithe,” a fuzzy merge with a threshold of 80% may result in a match being made. Overall, fuzzy matching in Power BI enables more accurate and efficient data analysis by identifying and linking similar data points.
Perform Fuzzy Matching in Power BI (With Example)
Often you may want to join together two tables in Power BI based on imperfectly matching strings. This is referred to as fuzzy matching.
The easiest way to perform fuzzy matching is to use the Merge Queries function in the Power Query Editor.
The following example shows how to do so in practice.
Example: How to Perform Fuzzy Matching in Power BI
Suppose we have the following table in Power BI named data1 that contains information about the team name and points scored for various basketball players:
And suppose that we have another table named data2 that contains information about the team name and assists for various basketball players:
Suppose that we would like to perform an inner join between the two tables in which we use a “fuzzy match” on the strings in the Team columns of each table.
To do so, click the Home tab along the top ribbon, then click the Transform data icon:
This will bring up the Power Query Editor.
Next, click the Merge Queries icon in the Combine group of the Home tab.
Then click Merge Queries as New from the dropdown menu:
In the new window that appears, choose data1 as the first table, choose data2 as the second table, and choose Inner as the Join Kind.
Then check the box next to Use fuzzy matching to perform the merge:
You can also specify the Similarity threshold value if you’d like, which ranges between 0 and 1.
A value of 0 would match any strings and a value of 1 would only match strings that are exactly the same. The default value is 0.8.
Once you click OK, the inner join will be performed based on fuzzy matches in the Team columns of the two tables:
Next, click the left and right arrows on the header of the data2 column. Then check the box next to Assists to indicate that this column should be the only one included from data2 in the final merged table:
Once you click OK, the Assists column will be shown from the data2 table:
Once you exit out of the Power Query Editor, a message box will appear that asks if you’d like to apply your changes.
Click Yes.
You will then be able to see the new table named Merge1 in the Table view:
Notice that this final merged table was able to match each string in the Team column of data1 with a string in the Team column of data2 based on fuzzy matching.
Note: If you’d like, you can right click on the header named data2.Assists and rename the column to just Assists.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: