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"
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.
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!!
- lbendlin4 years agoSuper User
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?
- jessimica10184 years agoHelper II
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?
- lbendlin4 years agoSuper User
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.