The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi all,
I'm currently trying to do a fuzzy match in Power Query. I have two tables - one that contains a bunch of columns that have customer data in them and specifically a customer name column. The other table has one column only and it's all of the customers names our company has ever had. I'm trying to get a fuzzy match so I can make sure with this new promotion we are running that the new customers are in fact new customers and there isn't a mispelling anywhere to mess up the count. When I use the fuzzy matching feature I try to merge both tables by selecting the customer name column in both tables, left outer join and set the tolerance for the standard 0.80. However, it doesn't create a new column that I can bring on the view either in the original table or when I create a new "Merged" table. It doesn't seem like it's doing anything at all to be honest.
Does anyone know what I'm missing? Thanks in advance!
Hi,
Share some data to work with. Share data in a format that can be pasted in an MS Excel file.
What if I want it to produce a new column that provides the match so I can see them side by side to see the differences? Essentially I'm trying to isolate which ones are new customers and which ones aren't using the fuzzy match. I'm not 100% sure if this is the best way to do it in Power BI and there may be a better way but this is what I came up with in my research.
Go to the full list.
Use the Merge options as before.
This time choose the Left Join that will return all rows from the ALL customers table and Matching from the Old customer table.
A column called Old Customer will be created click on the expand arrows to the right of the column.
From the expand option you can choose which columns to pull through from the other table.
Alternitively if you don't need any data from the other table you could choose Aggregate.
Then set it to count the customer ID or name.
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Hi @NCPowerBI
Go to the table you want to REMOVE the rows from and choose Merge.
Set this to Left ANTI and the number of matches to 1.
Highlight the two columns.
Here you can see I am EXCLUDING 49 rows that exist in the second table but not the first.
This produces a new column but it will be empty, you can just delete or ignore it.
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.