Forum Discussion

jessimica1018's avatar
jessimica1018
Helper II
4 years ago
Solved

Lookup for multiple columns

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 an...
  • lbendlin's avatar
    lbendlin
    4 years ago

    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"