How to get data from two table with different formats of an employees name.

[RobKay12]

Hello,

I have two tables with two different ways of showing employee's names and I ultimatly want to count how many times their names show up in the two tables. For example:

     

I can do it by splitting all the cells and just looking for the first and last name only but was wondering if there was an easier way to complete this.

Any help is appreciated.

Thanks

[selimovd]

Hey @RobKay12 ,

I would first split the name in Last Name and First Name.

This is improving the match later.

Afterwards you choose one table that is the most complete (has all name). Then you can use the fuzzy merge to get the amount of matches from table 1 and another merge to get the amount of people from table 2.

Then you just have to sum them.

Check the attached file, I did a demo.

Here the screenshots of the 2 tables:

And then the final table:

And then the M-Code for the merge:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7NLMnQUfDKz8hTcNRTitWJVnJJLC/Oz9NRcCtKzMtWcMzJSc0Di3sklqcW6yh4p+blpZZkKHgBVccCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Last Name", each Text.BeforeDelimiter([Name], ","), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text After Delimiter", each Text.AfterDelimiter([Name], ","), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text After Delimiter",{{"Text After Delimiter", "First Name"}}),
    #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"First Name", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"First Name", Text.Clean, type text}}),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Cleaned Text", {"Last Name", "First Name"}, #"Table 2", {"Last Name", "First Name"}, "Table 2", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.4]),
    #"Aggregated Rows Table 2" = Table.AggregateTableColumn(#"Merged Queries", "Table 2", {{"Name", List.Count, "Rows Table 2"}}),
    #"Merged Queries1" = Table.FuzzyNestedJoin(#"Aggregated Rows Table 2", {"Last Name", "First Name"}, #"Table 1", {"Last Name", "First Name"}, "Table 1", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.4]),
    #"Aggregated Table 2" = Table.AggregateTableColumn(#"Merged Queries1", "Table 1", {{"Name", List.Count, "Rows Table 1"}}),
    #"Added Custom" = Table.AddColumn(#"Aggregated Table 2", "Total Rows", each [Rows Table 2] + [Rows Table 1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Last Name", "First Name", "Rows Table 2", "Rows Table 1"})
in
    #"Removed Columns"

You might have to adjust the threshold in the fuzzy merge. In this case it's a little tricks as a threshold of 0.3 will keep the merge of Ken Hawes, what is correct, but will also merge Frank Dawsen to John Smith. Maybe you have to adust with other metrics.

Please find attached the file for this example.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[RobKay12]

Thank you. I was also thinking about the Fuzzy Merge but wasn't sure how accurate that would have been. I will play with it to see what I get.

Appreciate the reply.