Lookup for multiple columns

[jessimica1018]

Hey all! I am creating phone anaylsis reports for our recruiting team but have run into some issues. Our phone vendor's raw data is just gross but cannot be changed; all it contains is some emails and some phone numbers with call time and directionality of the call (few other fields but there are the only that matter currently).  I am trying to add names, divisions, departments...etc...  from our HCM system, which I super dynamic and call pull in any piece of data needed. However I am having issues getting the two to talk and pull data in nicely. I need a best practice here:

Phone Data:    Internal (basically corporate) staff has User ID's as their email address but our external staff  (contractors) do not have user ID's in the phone data but we do have phone numbers.

From User ID	From Number	To User ID	To Number	Direction	Date	Total Minutes
john@email.com	1112223333		1231231234	OutBound	3/2/22	11
Jane@email.com	1112223334		1231231235	OutBound	2/27/22	12
	1231231235	Jane@email.com	1112223334	InBound	2/28/22	20
	1231231234	john@email.com	1112223333	InBound	3/1/22	16

HCM Data:  We have all the data

Employee Name	Email	Home Number	Work Number
John Internal	john@email.com		1112223333
Jane Internal	Jane@email.com		1112223334
Person1 External	Person1@anotherplace.com	1231231234
Person2 External	Person2@anotherplace.com	1231231235

Ultimate End Result so I can build a beautiful dashboard:

From	To	Direction	Date	Total Minutes
John Internal	Person1 External	OutBound	3/2/2022	11
Jane Internal	Person2 External	OutBound	2/27/2022	12
Person2 External	Jane Internal	InBound	2/28/2022	20
Person1 External	John Internal	InBound	3/1/2022	16

I have my models connected by "From User ID" and "Email"; but then it's not picking up those external numbers nor is it picking up the phone calls "To" anyone. I have been racking my brain on how to make this connect properly. I've done a DAX Vlookup but and  could get "From User ID" to pull correctly but all other fields didn't work. I tried Merging Queries but I don't think that is the best way since it's more of a "If this cell is blank then vlookup this column" type deal. 

Anyone have any recommendations on how to make this mess work??? 

Thank you!!!  

[lbendlin]

If you want to limit yourself to DAX you will need to run multiple LOOKUPVALUE queries.

In Power Query you have much more flexibility. You can use Table.AddColumn with a custom generator function to implement fuzzy joins across multiple columns with all kinds of weird rules.  It ain't fast, but is extremely powerful.

[jessimica1018]

I am for sure your answer is the solution! However I have never done a fuzzy join across multiple columns. Can you point me in the right direction (video, article...etc)? I kept trying to find how with the information you provided but just couldn't find the right thing I think.

Thank you!!

[lbendlin]

I think I have posted this example a couple of times. Here's the basic premise:

let
    Source = Table.AddColumn(TableA, "Match", 
        (k) => 
                Table.SelectRows(TableB,
                        each ([Column1]="*" or k[Column1]=[Column1])
                             and ([Column2]="*" or k[Column2]=[Column2])
                             and ([Column3]="*" or k[Column3]=[Column3])
                )
    ),

In the custom generator function you specify whatever whacky join logic you have.  In the next step you then expand the result as needed.

What are your join rules?

[jessimica1018]

Currently my join rule are Many to One for the 'Phone Data' [From User ID] to 'EE Data' [Email Address]; should keep that join active? 

[lbendlin]

Don't confuse data model joins (in Power BI)  with table merges in Power Query. In Power BI you can only join on a single column.

[jessimica1018]

Ah Got it! I have any table merges in Power Query, I thought about doing that but wasn't sure if it was worth it since I'm needing to look up so many different columns. I can definitely merge if that's my best bet here.

[lbendlin]

It's not your best bet.  Performance will be horrible.  Best would be to do this in the data source.

[jessimica1018]

I promise I am smarter then this ....usually

okay okay; so I just tried one line of it just for testing purposes but the several ways I have tried I get the following errors.

Token Comma Expected (I have flipped the two tables several times to see if that was it, it says source is the error)

   Source  = Table.AddColumn(#"EE Data", "Match",

        (k) =>

                    Table.SelectRows(#"Phone Data",

                             and ([To Number]="*" or k[To Number]=[To Number])

                             and ([From Number]="*" or k[From Number]=[From Number])

                )

    ).

Or

Token Eof expected  (I think thats my fault of placement though)

I even tried creating a blank query and it pulls in the tables fine but the column then has an error saying it can't find the "To Number" column (Again I flipped the table names to see if that was it).

[lbendlin]

Provide sample data for the two tables you want to merge, describe the merge rules, and show the expected outcome.

[jessimica1018]

Documents for dashboard 

Hopefully that link works!

I greatly appreciate all your help!!!

[lbendlin · Accepted solution]

The link works. Now please describe the rules for the matching.  Here is an example based on just the phone numbers (and adding to the Employees table).  

let
  Source = Excel.Workbook(File.Contents("c:\users\xxx\Downloads\EE Data.xlsx"), null, true), 
  Page1_Sheet = Source{[Item = "Page1", Kind = "Sheet"]}[Data], 
  #"Promoted Headers" = Table.PromoteHeaders(Page1_Sheet, [PromoteAllScalars = true]), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers", 
    {
      {"Company Code", type text}, 
      {"Employee Number", Int64.Type}, 
      {"Current Status", type text}, 
      {"Employee Name", type text}, 
      {"Email Address", type text}, 
      {"Home Phone", Int64.Type}, 
      {"Work Phone", Int64.Type}
    }
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Calls", 
    (k) =>
      Table.SelectRows(
        #"Phone Data", 
        each [From Number] = k[Home Phone] 
          or [From Number] = k[Work Phone] 
          or [To Number] = k[Home Phone] 
          or [To Number] = k[Work Phone]
      )
  )
in
  #"Added Custom"

You may not want that, instead you may want to add a column to the phone records table that tags the employee.

let
    Source = Csv.Document(File.Contents("C:\users\xxx\downloads\Phone Data.csv"),[Delimiter=",", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"LinkedID", type text}, {"From User ID", type text}, {"From Number", Int64.Type}, {"To User ID", type text}, {"To Number", Int64.Type}, {"Direction", type text}, {"Date", type date}, {"Minutes", Int64.Type}, {"Disposition", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom",  (k)=> 
Table.SelectRows(#"EE Data",
each k[From Number]=[Home Phone]
   or k[From Number]=[Work Phone]
   or k[To Number]=[Home Phone]
   or k[To Number]=[Work Phone])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Email Address"}, {"Email Address"})
in
    #"Expanded Custom"

[jessimica1018]

1. You are a genius!

2. I am really mad at myself ..... I had something similar earlier and what was causing my error .... a comma ....

I cannot thank you enough!!!!!!

Thank you!!!!!!