Calculating Columns Using Complex Algorithms: Fuzzy Matching

In the previous chapter, we discussed the importance of distance measures in estimating the dissimilarity between two distinct strings. Continuing our exploration of data analysis techniques, this chapter delves into the world of fuzzy matching, a technique used to determine logical similarities and identity mismatches in duplicates. Unfortunately, finding a dissimilarity metric in string values can be challenging. However, Power BI comes with a complex, reliable, and scalable fuzzy matching algorithm implemented by the Microsoft Research team based on the Jaccard distance. Although this algorithm performs well enough for typical fuzzy matching problems, it’s worth noting that there are other methods available if you require more precision and control. In this document, we will explore the topic of probabilistic data association, which is another powerful tool for your analytics arsenal. We will use a probabilistic...

Technical requirements

This chapter requires you to have a working internet connection and Power BI Desktop already installed on your machine (we used version 2.114.664.0, 64-bit, February 2023). You must have properly configured the R and Python engines and IDEs as outlined in Chapter 2, Configuring R with Power BI, and Chapter 3, Configuring Python with Power BI.

Exploring default fuzzy matching in Power BI

Power BI provides advanced data matching and clustering capabilities through its fuzzy matching and fuzzy clustering tools. These tools are particularly useful for data analysts who need to compare and merge items from separate lists based on their similarity.

Fuzzy matching enables you to merge two tables in Power Query by identifying and matching similar items. On the other hand, fuzzy clustering leverages a fuzzy matching algorithm to group data points with similar values together. This is achieved by mapping the value of each column to the best-matched group.

Power Query provides users with the flexibility to apply both fuzzy matching and fuzzy clustering options during data transformations. Fuzzy matching options are available when merging two tables, while fuzzy clustering options can be accessed when adding a new column.

While Microsoft provides a simplified description of the algorithms used by Power BI saying that...

Introducing probabilistic record linkage algorithms

In addition to deterministic algorithms like the LSH seen above, there are probabilistic record linkage algorithms that use probabilistic techniques to determine the probability that two records refer to the same entity. In fact, they are specifically designed for fuzzy matching or linking records from different datasets. They typically involve calculating similarities between pairs of records based on various attributes (e.g., names, addresses, etc.) and assigning weights or probabilities to these similarities. Such algorithms then use these probabilities to make a decision about whether two records match or not.

One of the best-known algorithms of this type is the Fellegi-Sunter model proposed by Peter J. Fellegi and Alan B. Sunter in 1969. It has become a fundamental approach to probabilistic record linkage. The algorithm typically involves several steps:

1. Comparison of attributes: The algorithm compares the values...

Applying probabilistic record linkage algorithms

Deduplication is one of the key challenges in MDM and refers to the accurate identification and linkage of duplicate records. Unlike the case of email deduplication applied to a single field, as you saw in the previous chapter, it is often necessary to deduplicate records based on multiple fields that may be similar. In this section, we’ll see how to approach a case of deduplication using probabilistic record linkage in Python, R, and Power BI.

Applying probabilistic record linkage in Python

One of the most popular open-source Python libraries designed for probabilistic record linkage is Splink (https://github.com/moj-analytical-services/splink). It simplifies the process of implementing probabilistic record linkage by providing an easy-to-use and intuitive interface. It can efficiently handle large datasets, making it suitable for both small and large data applications.

Splink performs all data-linking operations...

Summary

In this chapter, you learned how the default fuzzy matching feature in Power BI works and what it is. You also learned how to use another fuzzy matching algorithm based on probabilistic data linkage by implementing it in both Python and R. You then applied this knowledge to a real-world deduplication case in Power BI.

In the upcoming chapter, you will delve into the world of optimization and learn how to tackle simple optimization problems by solving their associated Linear Programming (LP) models.

References

• Power Query Fuzzy Merge (https://learn.microsoft.com/en-us/power-query/merge-queries-fuzzy-match)
• Cluster values (https://learn.microsoft.com/en-us/power-query/cluster-values)
• Customizable and Scalable Fuzzy Join for Big Data (http://www.vldb.org/pvldb/vol12/p2106-chen.pdf)
• Locality Sensitive Hashing: How to Find Similar Items in a Large Set, with Precision (https://towardsdatascience.com/locality-sensitive-hashing-how-to-find-similar-items-in-a-large-set-with-precision-d907c52b05fc)
• Probabilistic record linkage (https://www.robinlinacre.com/probabilistic_linkage/)
• The mathematics of the Fellegi Sunter model (https://www.robinlinacre.com/maths_of_fellegi_sunter/)
• The Intuition Behind the Use of Expectation Maximisation to Train Record Linkage Models (https://www.robinlinacre.com/em_intuition/)

Test your knowledge

1. What is fuzzy matching?
2. What are the benefits and use cases of fuzzy matching in data analysis?
3. What are the limitations of the default fuzzy matching algorithm in Power BI?
4. What are the common data cleaning steps performed before applying fuzzy matching algorithms?
5. How does the algorithm implemented in Power BI overcome the computational complexity of comparing large datasets?
6. How does probabilistic record linkage work?
7. Which libraries are used in this chapter to apply probabilistic record linkage algorithms?
8. How can the accuracy and performance of probabilistic record linkage algorithms be evaluated?

Learn more on Discord

To join the Discord community for this book – where you can share feedback, ask questions to the author, and learn about new releases – follow the QR code below:

https://discord.gg/MKww5g45EB