Power BI – Fuzzy Match Transformation Table
By: David Rohlfs
Introduction
When you start working with Power Query there are a lot of difficulties, and one of the harder ones is working with combining, merging, and appending data. Using a Fuzzy Match can be helpful but there are some advanced options that are still complicated. One of these is the fuzzy match transformation table.
This blog will be built from existing knowledge that you can find in the Fuzzy Match and Merge and Append blogs that EPM Strategy has covered before. If you would like to read those, they are linked at the end of this page.
This blog will cover what a Fuzzy Match Transformation table is, when to use it, some problems that you might run into, and a walkthrough implementing this function.
When to use it
There are really two main scenarios where you will use a transformation table. The first is that you will need a table to relate all your values to a new value. Meaning that all your values will be present in the transformation table. The second is that not all your values will use the transformation table to relate to a new value. This could be one value not in the table, or all but one.
The first scenario where all values are represented is less likely to happen but is a great way to learn about the fuzzy match transformation table. In this example I am trying to relate animals to food. Because I do not have a relational column built into my tables, I am going to use a preexisting transformation table. Notice that my transformation table has a “To” and a “From” column. This is a requirement for a transformation table.
I know that this example isn’t very practical but think of the food and animal tables having another 200 columns and hundreds of thousands more rows. And although having a pre-existing transformation table is not likely, it is still very possible that this could happen to you. If you did not have a pre-existing transformation table, then you would need to create one.
After going through the process of using a fuzzy match transformation table, this is our final table.
The second scenario is much more practical in that you won’t have all values represented in your transformation table. Here is my sample data:
Notice that my transformation table only has a portion of the values listed. This is because all the values not listed will be able to join normally from the data in our originating tables. Now let’s see the result.
Common Problems
You do not get to have an “exact match” when using a transformation table.
When you are using a transformation table, you do not get to have an exact match. This means that you cannot change the similarity threshold to 1.00 because it will disregard your transformation table. I am not sure why this occurs, but it is important to keep it in mind when using a transformation table. For the examples in this blog, I have been able to keep all the similarity thresholds above 0.9 so that the key columns need to be very similar when the match occurs.
Multiple values on table A to one value on table B
You may need to incorporate a maximum number of matches into your fuzzy match. This process and explanation will be linked below in a different EPM Strategy blog on Fuzzy Match Maximum Number of Matches.
Walkthrough
This walk through will not cover a lot of important information for creating a merge or fuzzy match merge. If you want help getting caught up, I suggest reading through some of the other blogs on merging linked below.
We are going to start by making sure that we have a correctly set up transformation table. We need to have a column named “From” and a column named “To”. The from column will match the first tables key column data. And the to column will match the second tables key column data. You can name this table whatever you would like to, but I suggest unchecking the enable load option and creating a name that represents this is just a transformation table.
Once we have our transformation table ready, we can go into our merge pop out. In here make sure that you have the “Use fuzzy match to perform the merge” option checked, then expand the fuzzy match options to see the advanced options. If you are trying to perform an “exact match” or have the similarity threshold be 1.0, then look at the common problems above. Now, scroll down to the transformation table dropdown and select your table. Then click OK in the bottom right corner.
Finally, you should have your merged table appear. If you have problems with there being excess matches among your data, I suggest that you read through EPM Strategy’s blog on Fuzzy Match Maximum Number of Matches linked below.
Conclusion
I hope that you learned something new about fuzzy match transformation tables in this blog. If you enjoyed this or want to learn more about merging, there are several other EPM Strategy blogs linked below.
Links to Related Articles:
Fuzzy Match Maximum Number of Matches
Comments
Leave a Reply
You must be logged in to post a comment.