Power Query look-up by critera in chronological order

[Syndicate_Admin]

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

[wdx223_Daniel]

let
    Mapping = Excel.CurrentWorkbook(){[Name="Mapping"]}[Content],
    Actual = Excel.CurrentWorkbook(){[Name="Actual"]}[Content],
    Custom1 = Table.AddColumn(Actual,"Results",each Table.RowCount(Table.SelectRows(Mapping,(x)=>x[Country]=[Country] and (x[Region]=[Region] or x[Region]="ALL") and (x[City]=[City] or x[City]="ALL")))>0)
in
    Custom1

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdBNC8IwDAbgvzJ23mHz26s6RRQRdIjIDmHLtNilkk3Ef28mq1R2aPrSPpCQy8WPwjCUE/mBnxykLLVhBRKOWD7AT4Mf6TVkI2VnuEYmb82ogXJ5maEuoKpd3bf6BBoruefAuSoK1wxs0x2+vLPhuxO9uarfLh7KZ5xIWSGXQO9vW9aKXDSyXWO6trNtDeXmD427aKa4VHSD0nUT6w6ZqVu4eFKO6KqpnWvJQBlK2AOryiFR2B29wffiybKz9AM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, Country = _t, Region = _t, City = _t]),
    #"Lookup Table" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg1W0lFy9PGBkrE6QCFvIMc1Lz0nMS8FyPLJz0vJz0PIBCfnl0ClXFMy85JKi9IzsMq6lOalpKYipMITc1KL0e3xyy8qSS3KU/AsSoVqg0m7hgI57qlFuYl5lTDhWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Region = _t, City = _t]),
    #"Lookup List" = List.Transform(Table.ToRows(#"Lookup Table"), each List.RemoveItems(_, {"ALL"})),
    MatchUp = Table.AddColumn(Source, "Result", each let lookup = List.Skip(Record.ToList(_),1) in if List.Contains(#"Lookup List", {lookup{0}}) then "Yes" else if List.Contains(#"Lookup List", List.FirstN(lookup,2)) then "Yes" else if List.Contains(#"Lookup List", lookup) then "Yes" else "No")
in
    MatchUp

[Syndicate_Admin]

Hi thanks I think it works really well but just wondering if you can upload the working file so I can play around the coding.

Also not sure if you could help explain slightly just the main formula being used and logics behind so I can apply to other data and tables.

Thanks so much for your help!!