Forum Discussion
Fuzzy Merge - With Two Columns (one with exacth match and other with fuzzy match)
- 1 year ago
So I understood correctly that you have 100K employees.
But he size of the departments (companies) is 10 times as large as I assumed.So I tested with 100K employees in 288 departments with on average around 350 emplyees each. I used a normal ditribution for the number of employees per department so that 90% is between 300 and 400 employees.
And I have some good news for you!It is even quicker! It is now about 1 minute on my laptop.
So the performace of the FuzzyJoin is not lineair with the number of comparisons it needs to make, but gets relatively more efficient with a bigger dataset.
Here the final query:
let Source = #"Employee Data", #"Grouped Rows" = Table.Group(Source, {"Department_Id"}, {{"Employees", each _, type table [Department_Id=nullable number, Employee_Id=nullable number, Employee_Name=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each // the following executes for each row. let // to break up a otherwise complex fromula (does not impact perfomance) // load this department employees in memory employee_buffer = [Employees], // Table.Buffer([Employees]), // tried buffering, but did not make any difference // Create a table with matching employee names Matched = Table.FuzzyNestedJoin(employee_buffer, {"Employee_Name"}, employee_buffer, {"Employee_Name"}, "Fuzzy Matched Employees", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=false, Threshold=0.9, NumberOfMatches=1]), // Add the Employee Id's and Employee Names of the matches expanded = Table.ExpandTableColumn(Matched, "Fuzzy Matched Employees", {"Department_Id", "Employee_Id", "Employee_Name"}, {"Fuzzy Matched Employees.Department_Id", "Fuzzy Matched Employees.Employee_Id", "Fuzzy Matched Employees.Employee_Name"}) in // Select only the employees not matching themselves and return the outcome Table.SelectRows(expanded, each [Fuzzy Matched Employees.Employee_Id] <> [Employee_Id]) ), // Get the Employee_id and Employee Names into the departments table #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Employee_Id", "Employee_Name", "Fuzzy Matched Employees.Employee_Id", "Fuzzy Matched Employees.Employee_Name"}, {"Employee_Id", "Employee_Name", "Fuzzy Matched Employees.Employee_Id", "Fuzzy Matched Employees.Employee_Name"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [Employee_Id] <> null) in #"Filtered Rows"Please mark this as solution if you can use it (even if it only helps you to decide NOT to use PowerQuery for the fuzzy matching...)
Thanks Omid_Motamedise
I was thinking about same solution but my data is huge (100K+ rows) and concenred about performance issue with fuzzy merge based on Employee_Name only but not by Department_Id wise.
Step#1: Initial process takes time to merge across all departments
Step#2: Each row comparison to check for department ids are same or not
Step#3: Filterout rows where department ids are not same
Step#4: Final output filter out rows with same employee id within each row
Also I'm expecting results to match with Excel Fuzzy Lookup Add-In output (department_id - exacth match, employee_name - default).
Best Regards,
Vinod
At my laptop again.
I took a different approach now.
- I first group the data in Employees by Department_ID with "keep all rows"
- This produces a Column Employees
- Then I create a new colum containing the fuzzy match of all employees of that department only
- I then in each fuzzy matched table i remove all matches of employees to themselves
- Finaly I remove all EDepartments with any matches
I tested it with a 100000 row Employee table.
This loads at about 60 rows per second. My test datset has 25 people in each department. Making departments larger or smaller may give different results.
My original query loaded a 6 rows/sec. So about a 10-fold speed improvement.
Here the M Code.
let
Source = #"Employee Data",
#"Grouped Rows" = Table.Group(Source, {"Department_Id"}, {{"Employees", each _, type table [Department_Id=nullable number, Employee_Id=nullable number, Employee_Name=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each // the following executes for each row.
let // to break up a otherwise complex fromula (does not impact perfomance)
// Create a table with matching employee names
Matched = Table.FuzzyNestedJoin([Employees], {"Employee_Name"}, [Employees], {"Employee_Name"}, "Fuzzy Matched Employees", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=false, Threshold=0.2]),
// Add the Employee Id's and Employee Names of the matches
expanded = Table.ExpandTableColumn(Matched, "Fuzzy Matched Employees", {"Department_Id", "Employee_Id", "Employee_Name"}, {"Fuzzy Matched Employees.Department_Id", "Fuzzy Matched Employees.Employee_Id", "Fuzzy Matched Employees.Employee_Name"})
in
// Select only the employees not matching themselves and return the outcome
Table.SelectRows(expanded, each [Fuzzy Matched Employees.Employee_Id] <> [Employee_Id])
),
// Get the Employee_id and Employee Names into the departments table
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Employee_Id", "Employee_Name", "Fuzzy Matched Employees.Employee_Id", "Fuzzy Matched Employees.Employee_Name"}, {"Employee_Id", "Employee_Name", "Fuzzy Matched Employees.Employee_Id", "Fuzzy Matched Employees.Employee_Name"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [Employee_Id] <> null)
in
#"Filtered Rows"
- PwerQueryKees1 year ago
Super User
Total load time for 8069 matches was 2:35. So on average 52 matches per second.
- vinod_excel1 year agoFrequent Visitor
Thanks PwerQueryKees
2:35 minutes is huge time for 8K matches. Mine is 80K+ records so it would be 10 times to process and the data keep on growing. So in current volume it takes around 20+ minutes.
- PwerQueryKees1 year ago
Super User
EDIT: Halving the number of matches is next to impossible because you can't controll the FuzzyNestedJoin() to consider only a part of the records.
I understood from your ealier message that your total datset was 100K rows. So 8K matches seemed reasonable.
If you have 80K duplicate names out of 100K this means (almost) every employee has a duplicate.
Anyway...
The speed of my solution scale lineair with the number of departments and quadratic with the (average) number of employees per department. The solution Omid_Motamedise gave you is quadratic with the total number of employees.
So Omid's appraoch requires 100K * 100K = 10 miljon matches,
My approach requires 4000 * 25 * 25 = 4K * 225 = 900K matches
And keep in mind I tested on my laptop. Your hardware maybe be more capable ...
You could possible cut the times in half again if you do every match only once. So if you match E1 with E2 you do not need to match E2 to E1. So a bit over a minute.
I would add an index to each departments employee table and only match each employees name with the employees having a higher index.
If you want the matchin name to appear on both employees, you would need to do that at the end, adding some additional time...No time at the moment to work that one out for you...
But if
- you specify the exact number of departments and
- the average and stddeviation of the number of employees in each department (although I suspect your actual table does not contains employees 😁) and
- you have a target elapse time for me,
I could take a swing at it. Although I would be really surprised (and proud) to get it below 1 minute 15 secs.