Power Query look-up by critera in chronological order

Question

Hi, there.

Wondering if PQ allows vlookup by multiple criteria where follow certain order, for example my mapping table is on the left,

• Basically I would like PQ to vlookup or fill in the Results column in table B follow logic in table A mapping table
• For any US regions, fill in Yes and no need to look at region or city
• For any cities in England, only London will be yes
• For any other cities in England, although no listed in the table, will be a empty cell (or no)

Current solution as below, not sure if PQ allows 1 mapping query in chronological order?

• Split the mapping table to 3 files and merge separately
• i.e. 1st query match "Country" between 2 tables and all US = Yes, remaining = empty
• 2nd query - match regions i.e. Wales,NI and Germany = Yes, remaining=empty
• 3rd query - city matching crietria
• combine all sub tables to one

119641-sample-file.pdf

Answer 1

One way to do that:

• Join (NestedJoin) the two tables
• Filter the mapping table to return the mapping rows if the Region matches or if it is "ALL"
• Filter that filtered table for the City or All, and return the Results column.
  • If the second filtering => no rows, or if Result is not Available, then No else Yes

M Code

   let  
       Source = Excel.CurrentWorkbook(){[Name="Mapping"]}[Content],  
     mapping = Table.TransformColumnTypes(Source,{  
           {"Country", type text}, {"Region", type text}, {"City", type text}, {"Results", type text}  
           }),  
     map = Table.RenameColumns(mapping, List.Transform(Table.ColumnNames(mapping),each {_,"M." & _})),  
       
       Source2 = Excel.CurrentWorkbook(){[Name="Actual"]}[Content],  
     actual = Table.TransformColumnTypes(Source2,{  
           {"Order", Int64.Type}, {"Country", type text}, {"Region", type text}, {"City", type text}  
           }),  
     join = Table.NestedJoin(actual,"Country",map,"M.Country","Join",JoinKind.LeftOuter),  
       
     //Filter the Region for named region or ALL  
       #"Added Custom" = Table.AddColumn(join, "inRegion", each   
         let   
           str = [Region]  
         in  
           Table.SelectRows([Join], each [M.Region] = "ALL" or [M.Region] = str)),  
     
     //Filter for City or ALL + Available  
     //  If rowcount > 0 then "Yes"  
       #"Added Custom1" = Table.AddColumn(#"Added Custom", "Results", each   
         let   
           str = [City]  
         in  
             if(Table.RowCount(  
               Table.SelectRows([inRegion],   
                 each [M.City] = "ALL"   
                   or [M.City] = str   
                   and [M.Results] = "Available")))   
               > 0 then "Yes" else "No"),  
         
       //remove unneeded columns  
       #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Join", "inRegion"})  
   in  
       #"Removed Columns"  

`