Forum Discussion

vinod_excel's avatar
vinod_excel
Frequent Visitor
1 year ago
Solved

Fuzzy Merge - With Two Columns (one with exacth match and other with fuzzy match)

colinlewissmith Amaro Vijay_A_Verma 

 

Hi All,

 

I've single table (Employee) to perform Fuzzy Merge/Match on Employee_Name within department_id, so that I can see matching / duplicate employee names side by side within same department.

 

Here is the table structure.

  1. Department_Id
  2. Employee_Id
  3. Employee_Name

How to perform exatch match on 'Department_Id' and fuzzy match on Employee_Name?

 

Best Regards,

Vinod

  • 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...)

21 Replies

  • To do this, you need to apply the fuzzy merge based on Employee_Name only, then in the resulted rows, filter (by using Table.SelectRows) the resulted rows with the same  Department_Id.

    If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos.
    Thank you!

    • vinod_excel's avatar
      vinod_excel
      Frequent Visitor

      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

      • Omid_Motamedise's avatar
        Omid_Motamedise
        Icon for Super User rankSuper User


        When dealing with large datasets, using fuzzy merge can become time-consuming. Before opting for fuzzy merge, consider extracting the similar parts directly, as this may simplify the process and solve the problem in one step. If possible, please provide a sample of the data so I can offer more tailored assistance.

        Additionally, you can use Table.Buffer to improve filtering performance, though its effectiveness also depends on your data structure.

  • Not at my laptop. 

    I would do someting like:

    • Merge the Table with itself on Department_Id calll it DepartmentMembers
    • Add a column and in the colum expression MergeJoin (fuzzy) DepartmentMembers column with itself on the Employeename
    • Expand the new column for Employee_name and the merge result column
    • Expand the Merge Result Column for Employee Name

    This will result in a merge for every row in your table, so it might not be very fast....

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi vinod_excel ,

    Power Query does not support exact and fuzzy matching at the same time. You must either perform exact matching on both columns or perform fuzzy matching on both columns.
    Please try the solutions provided by Omid_Motamedise and PwerQueryKees , If it works, please accept their response as the solution. If this doesn't solve your problem, please provide some sample data and the corresponding expected results so that we can try to find a workaround for you.

    Best Regards,
    Dino Tao

  • At my laptop now...

     

    Starting with

    Here the M-Code (all but the final filter condition completly done with the UI

    let
        Source = Excel.CurrentWorkbook(){[Name="Employee"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Department_Id", Int64.Type}, {"Employee_Id", Int64.Type}, {"Employee_Name", type text}}),
        #"Merged Queries1" = Table.FuzzyNestedJoin(#"Changed Type", {"Employee_Name"}, #"Changed Type", {"Employee_Name"}, "Fuzzy Matched Employees", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=false, Threshold=0.2]),
        #"Expanded Fuzzy Matched Employees" = Table.ExpandTableColumn(#"Merged Queries1", "Fuzzy Matched Employees", {"Department_Id", "Employee_Id", "Employee_Name"}, {"Fuzzy Matched Employees.Department_Id", "Fuzzy Matched Employees.Employee_Id", "Fuzzy Matched Employees.Employee_Name"}),
    
        // Result includes matches accross depratments and each employee always matches itself. 
        // Select all rows where De Department is the same as the matched departement and the empoyee id differes from the machted employee
        #"Filtered Rows" = Table.SelectRows(#"Expanded Fuzzy Matched Employees", each [Fuzzy Matched Employees.Department_Id] = [Department_Id] and [Fuzzy Matched Employees.Employee_Id] <> [Employee_Id])
    in
        #"Filtered Rows"

    Resulting in only 3 matches found.

     

    play around with the Threshold of the FuzzyNestedJoin to get more or fewer matches....