Forum Discussion
How to Find Lookup Values in Multiple Tables in Power Query
Hi I am trying to apply lookup on a table to find corresponding values from another table. But since there are null values I want to apply another lookup using a different column in the main table and finding those values in a different lookup table. Though I am able to achieve the 1st problem but unable to apply multiple lookups.
Its the same as applying two vlookups on mutliple table by using multiple columns if a value isn't coming from the 1st column.
Edit:
What I am trying to do is to create an equivalent of this excel formula that I am current using:
=IFERROR(IFERROR(VLOOKUP([@[Account Name]],Table15,5,FALSE),VLOOKUP([@Industry],Table14,2,FALSE)),"Industry Unknown")
I am very new to the M world so can't figure it out. Though I was able to write this code for just one lookup table:
(let AccountName = [Account Name] in Table.SelectRows(#"Account to Segment Mappings", each [Account Name] = AccountName)){0}[Customer Segment]
In power query I have imported two lookup tables - Account to Segment Mappings (Table 15) and Industry to Segment Mappings (table 14).
I am now trying to map Account name in my main table to account name in table 15 and if its not there then lookup Industry column in main table and check in table 14. If it still doesn't find a value return "Industry Unknown"
Main Table
| ABC Bank | Financial | |
| Z Company | Merchants | |
| D Company | Merchants | |
| A Company | Energy/Utilities |
Account to Segment Mapping Table
| ABC Bank | Financial |
| Z Company | Merchant & Commerce |
| D Company | Digital Partner |
Industry to Segment Mapping Table
| Financial | Financial |
| Energy/Utilities | Merchant & Commerce |
| Merchants | Digital Partner |
The excel code sees account name in Account to Segment Mapping Table using its account name column. If it is not there then using industry column tries to find customer Segment in the industry mapping table. If it isn't found there as well it gives "industry unknown"
1 Reply
Hi -
Here is one way it might be done. There may be a more concise method but if you step through this in the Advanced Editor you will understand what I am doing here:
let
Qry1 = Table.Join(MAIN, "Account_Name", Table.PrefixColumns(CUSTOMER_SEGMENT, "CUSTOMER_SEGMENT"), "CUSTOMER_SEGMENT.Account_Name", JoinKind.LeftOuter), // Left Join MAIN to CUSTOMER_SEGMENT
Qry2 = Table.Join(Table.SelectRows(Qry1, each _[CUSTOMER_SEGMENT.Account_Name] = null), "Industry", Table.PrefixColumns(INDUSTRY_TO_SEGMENT, "INDUSTRY_TO_SEGMENT"), "INDUSTRY_TO_SEGMENT.Industry", JoinKind.LeftOuter), // Select those that dont have a match and join to INDUSTRY_TO_SEGMENT
Qry3 = Table.SelectRows(Qry2, each _[INDUSTRY_TO_SEGMENT.Customer_Segment] = null), // Select those that still don't have a match
MAIN_MATCHES = Table.RenameColumns(Table.SelectColumns(Table.SelectRows(Qry1, each _[CUSTOMER_SEGMENT.Account_Name] <> null),{"Account_Name", "Industry", "CUSTOMER_SEGMENT.Customer_Segment"}), {"CUSTOMER_SEGMENT.Customer_Segment", "Customer_Segment"}), // Clean up Qry 1, selecting only the matches
INDUSTRY_MATCHES = Table.RenameColumns(Table.SelectColumns(Table.SelectRows(Qry2, each _[INDUSTRY_TO_SEGMENT.Customer_Segment] <> null),{"Account_Name", "Industry", "INDUSTRY_TO_SEGMENT.Customer_Segment"}),{"INDUSTRY_TO_SEGMENT.Customer_Segment", "Customer_Segment"}), //Clean up Qry 2, selecting only the matches
NULL_MATCHES = Table.RenameColumns(Table.SelectColumns(Qry3,{"Account_Name", "Industry", "INDUSTRY_TO_SEGMENT.Customer_Segment"}),{"INDUSTRY_TO_SEGMENT.Customer_Segment", "Customer_Segment"}), // Clean up Qry3
ALL_DATA = Table.ReplaceValue(Table.Combine({MAIN_MATCHES,INDUSTRY_MATCHES,NULL_MATCHES}),null, "Industry Unknown", Replacer.ReplaceValue, {"Customer_Segment"}) //Union them together, and replace null in Customer_Segment with Industry Unknown
in
ALL_DATAMy source data looks like this:
Hope this is helpful.
Cheers,
Peter
