How to Find Lookup Values in Multiple Tables in Power Query

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_DATA

My source data looks like this:

Hope this is helpful.

Cheers,

Peter