Forum Discussion
Lookup for multiple columns
- 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"
It's not your best bet. Performance will be horrible. Best would be to do this in the data source.
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).
- lbendlin4 years agoSuper User
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" - lbendlin4 years agoSuper User
Provide sample data for the two tables you want to merge, describe the merge rules, and show the expected outcome.
- jessimica10184 years agoHelper II
- jessimica10184 years agoHelper II
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!!!!!!