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...)
PwerQueryKees Thanks again for taking time to elaborate the solution.
Your suspect is not correct, I've employee names but in place of department_id I've company_ids.
I need to deal with 300+ company_ids and each company may have multiple employee_ids created for same employee with some text change in their name from time to time...The change may be at positions of first name, last name, middle name, first & last name combined without space or given with comma, earlier entry might be having achronym but latest name of same employee name provided with fullly abrivated, etc...
So I need to check for duplicate employee names within company_id and remap latest employee_id with old employee_id or vice versa.
Hope I made it clear with details that I was looking.
That clarifies your use case somewhat. So 300 companies. How many employees per compny on average? And are they very much of similar size or is there a lot of variation? What is the range of sizes for 90% of you companies? (So, ignoring the extremes?)
Keep in mind that 2.5 minutes to match a total of 100K employees is not bad. Even if you need to do this daily.
Let me know if you are still interested in a solution or maybe the soltion I gave you is satisfactory? Please mark it as solution in that case....