Matching Data that could be in different columns

[joooffice]

Hi,

I have data pulled from a form with a list of email addresses and names of people. I need to match them to data from a database to assign them to their account ID number. But people filling in the form may not complete the fields in exactly the same way.

Data from the form:

Email	Name
joe@joebloggs.com	Joe Bloggs
ben@ben.com	Ben Bread
tim@hotmail.com	Tim Smith
joe@joebloggs.com	Susan Bloggs

Data from the database: 

First Name	Nickname	Surname	Full Name	email 1	email 2	Account ID
Joseph	Joe	Bloggs	Joseph Bloggs	joe@joebloggs.com	susanbloggs@gmail.com	1234
Susan	Sue	Bloggs	Susan Bloggs	susanbloggs@gmail.com	joe@joebloggs.com	1234
Tim	Tim	Smith	Tim Smith	tim@hotmail.com		5479
Benjamin	Ben	Bread	Benjamin Bread	benjaminbread@gmail.com	ben@ben.com	4785

Because it is feasible that there may be more than one person with the same name, i cant not rely one column match so always needs to be email and name but there are 2 possible emails for each person. 

Joe Bloggs would match on Nickname, surname and email 1; Ben Bread would match on nickname, surname email 2; Tim smith would match on first name, surname, email 1; susan bloggs would match on first name surname and email 2.

I have tried doing it with a series of merges so matching on email 1 and full name and expanding the account id column; then matching on email 2 and full name; splitting the name column and email 1 and nickname; email 2 and nickname etc but i have to do so many merges to cover all the possibilities is taking so long to refresh the query and/or crashes.

How can i do this in power query M? Is there a way of conditional merging ie compare email 1 and full name if match then expand data to get ID and dont look at that row again, if they dont match look at all the rows that havent yet matched and  compare email 2 and full name and so on and so on so that everytime a row matches it doesnt get included again. 

there will be some that dont match against any of the criteria.

[Jakinta]

Here are my 2 cents 🙂

let
    Form = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyspPdQDipJz89PRiveT8XCUdJa/8VAUnsIBSrE60UlJqngMQQyWdUvMUnIpSE1PAciWZuQ4Z+SW5iZk5UPmQzFyF4NzMkgywPDbjg0uLE/PgFsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, Name = _t]),
    FormSplit = Table.SplitColumn(Form, "Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Name.1", "Name.2"}),
    DataBase = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY49D8IgEIb/CmFuTFSa6kY6uuJGGEAJpSmlkfb/e3y02kSHu9zzAC/HOb75oKcOVzBo6O3gjQkJo0cb915TKJX48PAOXFiCHLOhxkk7FH88nQkWFccsXgDBll120p/ofzG/vtyi7zZi7szZucuE1nm2jnZ+/opDUDVprul5q8deOhuXgzH2l5bPTOkArUIVoSLv9oMTClWINJcaC/EG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, Nickname = _t, Surname = _t, #"Full Name" = _t, #"email 1" = _t, #"email 2" = _t, #"Account ID" = _t]),
    DataBaseRows = Table.ToRows(DataBase),
    #"Added Custom" = Table.AddColumn(FormSplit, "Account ID", each let r=Record.ToList(_), d=DataBaseRows, t=List.PositionOf(List.Transform(d, each List.ContainsAll(_,r)), true) in List.Last(d{t})),
    FINAL = Table.CombineColumns(#"Added Custom",{"Name.1", "Name.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Name")
in
    FINAL

[joooffice]

Hi Jakinta

Thanks for this, I can't get it to work though. I get the following error 

Expression.Error: The index cannot be negative.
Details:
Value=[List]
Index=-1

I do have another column called ticket ID on my form and other columns on my database table - could that be causing the problem. Each person can appear more than once on the form as well - although only once on the database.

[jennratten]

Jakinta's solution is another way of handling this scenario for sure.  It will work as long as all records in the form data actually exist in the database and the names entered into the form data only include one space (meaning the names in the form data must not include any middle initials, last name suffixes, etc.), otherwise errors will be returned.  See the screensnips below.  I have added two records to the form data.  The first, George Jones, does not exist in the database.  The second, Fred F Smith, I also added to the database, but without the middle initial. 

I have used a few lines from Jankita's to my script and have tweaked the original error handling I included.

The error -1 is returned by List.PostionOf when it cannot find a match.

New Result without Errors

New Script

let
    // database data
    dbData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7BDoIwDIZfZdnZmKggcFs4ePCKN8JhaIUhY4bB+9uOgZBosnb9/q7/muf8aiy8a77DAjCnrakq65B0tnBjQGCUjvd3o1Gzo5XdpIhKS9V6/XA8BbzY5TyjByhk48bbyV/rfza/vlysb4pwyplWQz0Rm+tBaVGbYWXHMMIgStx4Cl0jtaLlsKTcg3xM5BpsFkovlMSb/bAjMDwFURw660sPNOeveR/CZbmnfAGQk4CVH54kjs68KD4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, Nickname = _t, Surname = _t, #"Full Name" = _t, #"email 1" = _t, #"email 2" = _t, #"Account ID" = _t]),
    dbData_ChangeTypes = Table.TransformColumnTypes(dbData,{{"First Name", type text}, {"Nickname", type text}, {"Surname", type text}, {"Full Name", type text}, {"email 1", type text}, {"email 2", type text}, {"Account ID", Int64.Type}}), 
    // transform the table to a list of lists.
    dbData_List = Table.ToRows ( dbData_ChangeTypes ),
    // form data
    formData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY3BCoMwEER/ZclZ+g0hBwte7S14iHUaY90sxPT/K1sLFnqYy7zHjPdmEdg94yoxbpe7sGlMJyCnhRkab0Zku+eADplcQZiU1cR2lsohrQe/JaaeU52V/5vvX1vI54MIKRGLxWnmqh11kvGRHuEJFX60tmCi9ns4vAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, Name = _t]),
    formData_ChangeTypes = Table.TransformColumnTypes(formData,{{"Email", type text}, {"Name", type text}}),
    // Extract delimited parts of the name, so they can be evaluated separately.
    // Take caution when using Text.Split as extra delimiters can throw off the results.
    // Ideally, the form fields would be similar in structure to the db to be matched, but that is not the case here.
    // Edit/add steps if additional delimited parts need to be accomodated, like name suffixes.
    formData_NamePart1 = Table.AddColumn(formData_ChangeTypes, "Name Part 1", each Text.BeforeDelimiter([Name], " "), type text),
    formData_NamePart2 = Table.AddColumn(formData_NamePart1, "Name Part 2", each Text.AfterDelimiter([Name], " ", {0, RelativePosition.FromEnd}), type text),
    // Add a list object to each row, containing each record's values for specific columns in the table.
    // If steps were added for additonal name parts, also add them to the list of fields (r) in this step.
    // Match the form data to the db data.
    add_dbAccount = Table.AddColumn (
        formData_NamePart2, 
        "Account ID", 
        each 
        let                                              // Variables
            r = Record.ToList (                          // List of specific fields 
                Record.SelectFields ( 
                    _, { "Email", "Name Part 1", "Name Part 2" } 
                )
            ),                                           // Current record values as a list
            d = dbData_List,                             // db list of lists 
            t = List.PositionOf (                        // Position of match in the db
                List.Transform (                         // --> list of 
                    d, each List.ContainsAll (           
                        _,                               // db lists
                        r,                               // containing form data elements
                        Comparer.FromCulture (           // Apply current culture 
                            Culture.Current, true        // with case insensitivity
                        )
                    )
                ), true 
            ),
            i = if t = -1                                // = -1 if no matches were found
            then null                                     
            else List.Last ( d{t} )                      
        in 
            i,
        Int64.Type 
    ),
    remove_columns = Table.RemoveColumns (
        add_dbAccount,
        { "Name Part 1", "Name Part 2" }
    )
in
    remove_columns

[joooffice]

Hi Jen

Thanks so much for this, the first version of the code you sent worked. I even figured out how to get it to pull other matching columns into the form table as wel but it is very slow to load (even slower than my original multiple merge method). 

This new version also works but it doesnt load the data in the Account ID column of the database table when it matches, it loads the last column in the database table which isn't the Account ID column. How can i change this?

there are 3 other columns that i want to load when it finds a match.

Joanne 

[jennratten · Accepted solution]

Great - I'm so glad we're making progress!  This line of the script determines which column number should be returned.

            else List.Last ( d{t} )  

 To get the column's position dynamically (instead of specifying the last column), add a step before add_dbAccount:

    column_dbPosition = List.PositionOf ( Table.ColumnNames ( dbData ), "Account ID" ),

And then reference it instead, like this:

else d{t}{column_dbPosition}  

Here is the complete new script with the column number dynamically referenced.

let
    // database data
    dbData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7BDoIwDIZfZdnZmKggcFs4ePCKN8JhaIUhY4bB+9uOgZBosnb9/q7/muf8aiy8a77DAjCnrakq65B0tnBjQGCUjvd3o1Gzo5XdpIhKS9V6/XA8BbzY5TyjByhk48bbyV/rfza/vlysb4pwyplWQz0Rm+tBaVGbYWXHMMIgStx4Cl0jtaLlsKTcg3xM5BpsFkovlMSb/bAjMDwFURw660sPNOeveR/CZbmnfAGQk4CVH54kjs68KD4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, Nickname = _t, Surname = _t, #"Full Name" = _t, #"email 1" = _t, #"email 2" = _t, #"Account ID" = _t]),
    dbData_ChangeTypes = Table.TransformColumnTypes(dbData,{{"First Name", type text}, {"Nickname", type text}, {"Surname", type text}, {"Full Name", type text}, {"email 1", type text}, {"email 2", type text}, {"Account ID", Int64.Type}}), 
    // transform the table to a list of lists.
    dbData_List = Table.ToRows ( dbData_ChangeTypes ),
    // form data
    formData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY3BCoMwEER/ZclZ+g0hBwte7S14iHUaY90sxPT/K1sLFnqYy7zHjPdmEdg94yoxbpe7sGlMJyCnhRkab0Zku+eADplcQZiU1cR2lsohrQe/JaaeU52V/5vvX1vI54MIKRGLxWnmqh11kvGRHuEJFX60tmCi9ns4vAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, Name = _t]),
    formData_ChangeTypes = Table.TransformColumnTypes(formData,{{"Email", type text}, {"Name", type text}}),
    // Extract delimited parts of the name, so they can be evaluated separately.
    // Take caution when using Text.Split as extra delimiters can throw off the results.
    // Ideally, the form fields would be similar in structure to the db to be matched, but that is not the case here.
    // Edit/add steps if additional delimited parts need to be accomodated, like name suffixes.
    formData_NamePart1 = Table.AddColumn(formData_ChangeTypes, "Name Part 1", each Text.BeforeDelimiter([Name], " "), type text),
    formData_NamePart2 = Table.AddColumn(formData_NamePart1, "Name Part 2", each Text.AfterDelimiter([Name], " ", {0, RelativePosition.FromEnd}), type text),
    // Add a list object to each row, containing each record's values for specific columns in the table.
    // If steps were added for additonal name parts, also add them to the list of fields (r) in this step.
    // Match the form data to the db data.
    column_dbPosition = List.PositionOf ( Table.ColumnNames ( dbData ), "Account ID" ),
    add_dbAccount = Table.AddColumn (
        formData_NamePart2, 
        "Account ID", 
        each 
        let                                              // Variables
            r = Record.ToList (                          // List of specific fields 
                Record.SelectFields ( 
                    _, { "Email", "Name Part 1", "Name Part 2" } 
                )
            ),                                           // Current record values as a list
            d = dbData_List,                             // db list of lists 
            t = List.PositionOf (                        // Position of match in the db
                List.Transform (                         // --> list of 
                    d, each List.ContainsAll (           
                        _,                               // db lists
                        r,                               // containing form data elements
                        Comparer.FromCulture (           // Apply current culture 
                            Culture.Current, true        // with case insensitivity
                        )
                    )
                ), true 
            ),
            i = if t = -1                                // = -1 if no matches were found
            then null                                     
            //else List.Last ( d{t} )     
            else d{t}{column_dbPosition}                 
        in 
            i,
        Int64.Type 
    ),
    remove_columns = Table.RemoveColumns (
        add_dbAccount,
        { "Name Part 1", "Name Part 2" }
    )
in
    remove_columns

Alternatively, you could use a function, like this: (I named the function fnGetDataFromExternalTable )

let
    fn = ( formsTable as table, dbTable as table, dbColumnName as text ) =>
let
    // database data
    dbData = dbTable,
    dbData_ChangeTypes = Table.TransformColumnTypes(dbData,{{"First Name", type text}, {"Nickname", type text}, {"Surname", type text}, {"Full Name", type text}, {"email 1", type text}, {"email 2", type text}, {"Account ID", Int64.Type}}), 
    // transform the table to a list of lists.
    dbData_List = Table.ToRows ( dbData_ChangeTypes ),
    // form data
    formData = formsTable,
    formData_ChangeTypes = Table.TransformColumnTypes(formData,{{"Email", type text}, {"Name", type text}}),
    // Extract delimited parts of the name, so they can be evaluated separately.
    // Take caution when using Text.Split as extra delimiters can throw off the results.
    // Ideally, the form fields would be similar in structure to the db to be matched, but that is not the case here.
    // Edit/add steps if additional delimited parts need to be accomodated, like name suffixes.
    formData_NamePart1 = Table.AddColumn(formData_ChangeTypes, "Name Part 1", each Text.BeforeDelimiter([Name], " "), type text),
    formData_NamePart2 = Table.AddColumn(formData_NamePart1, "Name Part 2", each Text.AfterDelimiter([Name], " ", {0, RelativePosition.FromEnd}), type text),
    // Add a list object to each row, containing each record's values for specific columns in the table.
    // If steps were added for additonal name parts, also add them to the list of fields (r) in this step.
    // Match the form data to the db data.
    column_dbPosition = List.PositionOf ( Table.ColumnNames ( dbData ), dbColumnName ),
    add_dbAccount = Table.AddColumn (
        formData_NamePart2, 
        dbColumnName, 
        each 
        let                                              // Variables
            r = Record.ToList (                          // List of specific fields 
                Record.SelectFields ( 
                    _, { "Email", "Name Part 1", "Name Part 2" } 
                )
            ),                                           // Current record values as a list
            d = dbData_List,                             // db list of lists 
            t = List.PositionOf (                        // Position of match in the db
                List.Transform (                         // --> list of 
                    d, each List.ContainsAll (           
                        _,                               // db lists
                        r,                               // containing form data elements
                        Comparer.FromCulture (           // Apply current culture 
                            Culture.Current, true        // with case insensitivity
                        )
                    )
                ), true 
            ),
            i = if t = -1                                // = -1 if no matches were found
            then null                                     
            //else List.Last ( d{t} )     
            else d{t}{column_dbPosition}                 
        in 
            i,
        Int64.Type 
    ),
    remove_columns = Table.RemoveColumns (
        add_dbAccount,
        { "Name Part 1", "Name Part 2" }
    )
in
    remove_columns
in 
    fn

Invoke the function:

fnGetDataFromExternalTable ( formsDataTable, dbDataTable, "Account ID" )

[Jakinta]

DataBase

Form

Result

M-code

let
    Form = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyspPdQDipJz89PRiveT8XCUdJa/8VAUnsIBSrE60UlJqngMQQyWdUvMUnIpSE1PAciWZuQ4Z+SW5iZk5UPmQzFyF4NzMkgywPDbjg0uLE/OQLaioAIpWKFQqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, Name = _t]),
    FormSplit = Table.SplitColumn(Form, "Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Name.1", "Name.2"}),
    DataBase = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY67DsIgFIZfhTA3JipNdSMdXXFrOnBLoSnFSPv+Hi5Vm+hwLt8H+aHr8M0H/TC4gkVDbyc/DCFh9OjNo9cUSiQ+SO/AhTXwORs6OG6n4o+nM4HBOe6rDrN4C5CtuweS/uT/y/r1bskXIuXfbXS5M2cXkwlt+2IdNX75ykRQNWmuMKRMGa2eR+5s/CassT81V5nSAdqEKEJE3v0UTihUIdJcahhK4b5/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, Nickname = _t, Surname = _t, #"Full Name" = _t, #"email 1" = _t, #"email 2" = _t, #"Account ID" = _t, Column1 = _t]),
    #"Added Custom" = Table.AddColumn(FormSplit, "Account ID", each let r=Record.ToList(_), d=Table.ToRows(DataBase), t=List.PositionOf(List.Transform(d, each List.ContainsAll(_,r)), true),c=Table.ColumnNames(DataBase), i=List.PositionOf( Table.ColumnNames(DataBase), "Account ID") in if t<0 then null else (d{t}{i})),
    FINAL = Table.CombineColumns(#"Added Custom",{"Name.1", "Name.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Name")
in
    FINAL

[jennratten]

Hello, here is a solution.  

let
    // database data
    dbData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY49D8IgEIb/CmFuTFSa6kY6uuJGGEAJpSmlkfb/e3y02kSHu9zzAC/HOb75oKcOVzBo6O3gjQkJo0cb915TKJX48PAOXFiCHLOhxkk7FH88nQkWFccsXgDBll120p/ofzG/vtyi7zZi7szZucuE1nm2jnZ+/opDUDVprul5q8deOhuXgzH2l5bPTOkArUIVoSLv9oMTClWINJcaC/EG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, Nickname = _t, Surname = _t, #"Full Name" = _t, #"email 1" = _t, #"email 2" = _t, #"Account ID" = _t]),
    dbData_ChangeTypes = Table.TransformColumnTypes(dbData,{{"First Name", type text}, {"Nickname", type text}, {"Surname", type text}, {"Full Name", type text}, {"email 1", type text}, {"email 2", type text}, {"Account ID", Int64.Type}}),
    // Add a list object to each row, containing each record's values for all columns in the table.
    dbData_AddRecordValuesAsList = Table.AddColumn(
        dbData_ChangeTypes, 
        "dbRecordValues",
        each Record.FieldValues ( 
            Record.SelectFields ( 
                _, Table.ColumnNames ( dbData_ChangeTypes ) 
            )
        )
    ),
    // form data
    formData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY29CoAgFEZfRe4cPYO4BK22iYPVxR+8XlB7/0IaGhrO8h04nzGQGOXDntn7Nh9MMMHKKNQYwE4Gdizy4ZUKi1AV3TlcjyQDd3Ixv36LJDTFHob/y+urufI98MjVY5L4ySxjEytYewM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, Name = _t]),
    formData_ChangeTypes = Table.TransformColumnTypes(formData,{{"Email", type text}, {"Name", type text}}),
    // Extract delimited parts of the name, so they can be evaluated separately.
    // Ideally, the form fields would be similar in structure to the db to be matched, but that is not the case here.
    // Edit/add steps if additional delimited parts need to be accomodated, like name suffixes.
    formData_NamePart1 = Table.AddColumn(formData_ChangeTypes, "Name Part 1", each Text.BeforeDelimiter([Name], " "), type text),
    formData_NamePart2 = Table.AddColumn(formData_NamePart1, "Name Part 2", each Text.AfterDelimiter([Name], " "), type text),
    // Add a list object to each row, containing each record's values for specific columns in the table.
    // If steps were added for additonal name parts, also add them to the list of fields in this step.
    formData_AddRecordValuesAsList = Table.AddColumn(
        formData_NamePart2, 
        "formRecordValues",
        each Record.FieldValues ( 
            Record.SelectFields ( _, { "Email", "Name Part 1", "Name Part 2" }
            )
        )
    ),
    // Match the form data to the db data.
    add_dbData_to_formData = Table.AddColumn (           // Add a column
        formData_AddRecordValuesAsList,                  // to this table (starting table)
        "dbDataTable",                                   // using this new column name.
        (formsTable) =>                                  // --> Current row iteration of the starting table.
        let varFilteredDb =                              // Store a variable for each FormsTable iteration. 
            Table.SelectRows (                           // Select rows
                dbData_AddRecordValuesAsList,            // of the db table
                (dbTable) =>                             // --> Current row iteration of the db table.
                List.ContainsAll (                       // in the values
                    dbTable[dbRecordValues],             // of the db list
                    formsTable[formRecordValues]         // contains all values in the forms list
                ) 
            )
        in varFilteredDb
    ),
    // Add a new column with the first value of the specified column.
    // If no match is returned or if the specific column does not exist then return null.
    add_dbAccount = Table.AddColumn (
        add_dbData_to_formData,
        "Account ID",
        each if Table.IsEmpty ( [dbDataTable] ) then null else [dbDataTable]?[Account ID]?{0},
        Int64.Type
    ),
    // Remove unnecessary columns
    remove_columns = Table.RemoveColumns (
        add_dbAccount,
        { "Name Part 1", "Name Part 2", "formRecordValues", "dbDataTable" }
    )
in
    remove_columns