Create a merge column by comparing 3 conditions to a 2nd table (Excel IFS/INDEX(MATCH) to PQ)

[memote1]

I have 2 tables, each contains an ID column and at least 1 email column. I need to get the ID's from PSTable and match to the individuals in TracerTbl. The majority of ID's are the same, but not all, so if they don't match, I need to use email to get the ID from PSTable and put it into TracerTbl. In Addition, I do need to be able to identify if there are any that I cannot match.

The Excel formula I accomplish this with is: =IFNA(IFS(AND([@UserID]>0,NOT(ISERROR(INDEX(PSTable_1[ID],MATCH([@UserID],PSTable_1[ID],0),1)))),[@UserID],NOT(ISERROR(INDEX(PSTable_1[ID],MATCH([@[Email Address]],PSTable_1[Email 1],0),1))),INDEX(PSTable_1[ID],MATCH([@[Email Address]],PSTable_1[Email 1],0),1),NOT(ISERROR(INDEX(PSTable_1[ID],MATCH([@[Email Address]],PSTable_1[Email 2],0),1))),INDEX(PSTable_1[ID],MATCH([@[Email Address]],PSTable_1[Email 2],0),1)),"Error")

Simplified TracerTbl

Email Address

UserID

Simplified PSTable

ID

Email 1

Email 2

Appreciate any help you can give!

Addendum: I tried merging by ID, then Email Address --> Email 1. It works, but when I expand the table, Email Address from TracerTbl disappears on almost everyone and the merge breaks. Advanced editor doesn't show why that's happening. (I renamed the first successful merged column, that didn't work. I also tried keeping ID and email, that didn't work either). 

[MarkLaf · Accepted solution]

I took a stab at this with the below dummy data. The approach is to merge on the columns you are interested in, then, rather than expand on the nested joins, perform a check against the merge column and perhaps pull out the related ID. When multiple rows are joined, we just take the first row and ignore any others, in line with MATCH's behavior (with 0 in last arg) in Excel.

TracerTbl

Email Address	UserID
one@place.com	1
two@place.com	2
three@place.com	null
four@place.com	4
five@place.com	null
six@place.com	99

PSTable

ID	Email 1	Email 2
1	dif1a@site.com	dif1b@site.com
2	null	dif2b@site.com
3	three@place.com	dif3b@site.com
4	four@place.com	null
5	dif5a@site.com	five@place.com
6	null	three@place.com

Power query (M) in advanced editor:

let
    <...>
    #"Removed Columns1" = <...>,
    MergeOnID = Table.NestedJoin(
        #"Removed Columns1", {"UserID"}, PSTable, {"ID"}, "ID_Check", JoinKind.LeftOuter
    ),
    MergeOnEmail1 = Table.NestedJoin(
        MergeOnID, {"Email Address"}, PSTable, {"Email 1"}, "Email1_Check", JoinKind.LeftOuter
    ),
    MergeOnEmail2 = Table.NestedJoin(
        MergeOnEmail1, {"Email Address"}, PSTable, {"Email 2"}, "Email2_Check", JoinKind.LeftOuter
    ),
    //For each merged column, perform a check calculation
    MergeCheckTransforms = Table.TransformColumns(
        MergeOnEmail2,
        {
            //Gives true/false on whether TracerTbl[ID] was found in PSTable
            {"ID_Check", each not Table.IsEmpty(_), type logical},
            //If no matches found on Email1, return null, otherwise return the ID of first row matched
            {"Email1_Check", each if Table.IsEmpty(_) then null else Table.First(_)[ID], Int64.Type},
            //If no matches found on Email2, return null, otherwise return the ID of first row matched
            {"Email2_Check", each if Table.IsEmpty(_) then null else Table.First(_)[ID], Int64.Type}
        }
    ),
    //Assign validated ID based on matches, with precedent order of: ID, Email1, Email2. 
    //If no matches at all, return null
    AddValidatedId = Table.AddColumn(
        MergeCheckTransforms,
        "Validated ID",
        each
            if [ID_Check] then
                [UserID]
            else if [Email1_Check] <> null then
                [Email1_Check]
            else if [Email2_Check] <> null then
                [Email2_Check]
            else
                null,
        Int64.Type
    )
in
    AddValidatedId

Here is the output. You can remove the Check columns or perform whatever other transforms as desired.

Email Address	UserID	ID_Check	Email1_Check	Email2_Check	Validated ID
one@place.com	1	TRUE	null	null	1
two@place.com	2	TRUE	null	null	2
three@place.com	null	FALSE	3	6	3
four@place.com	4	TRUE	4	null	4
five@place.com	null	FALSE	null	5	5
six@place.com	99	FALSE	null	null	null

Edit: Including screenshot of output as it's a little more readable:

[memote1]

Thank you so much!

[lbendlin]

Have you considered not doing this in Power Query but instead in the Power BI data model?

[memote1]

@lbendlin 

I haven't used PBI before, but I only have limited access to it. I know I don't have access to DAX. Once I finished this project, I was going to start exploring what I can do in PBI. What I am currently working on will combine 5 reports to create a CSV to import to a different program, so I didn't think PBI was the best avenue, but then again, I only know it for creating dashboards. 😕

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...