if match with multiple columns in Power Query

Question

I have a two tables, In Table 2 columns from B to F need to be matched from Table 1 columns from A to G. If matched then the status is "YES" if not matched then the status is "NO".

I am achieving the my output the following formula in Excel

G2=IF(AND(SUMPRODUCT((TABLE1!A$2:A$18=B2)*(TABLE1!B$2:B$18=C2))>0,IF(B2="Banana",D2=1,D2>1),SUMPRODUCT((TABLE1!B$2:B$18=C2)*(TABLE1!D$2:D$18=E2))>0,SUMPRODUCT((TABLE1!B$2:B$18=C2)*(TABLE1!E$2:G$18=F2))>0),"YES","NO")

but How can I apply the same in thing Power Query.

• Example for line item 2; (In Table 2 Data) The item is 123 the fruit list is "Avocado" with area code is "3100" it's matched to my master data Table 1 therefore the status is "YES".

• Example for line item 5; (In Table 2 Data) The item is 126 the fruit list is "Avocado" with area code is "West" it's not matched to my master data Table 1 therefore the status is "NO".

The "Avocado" "area code" belongs to only the following sales code (EU01,US,UK).

• Example for line item 19; The item is 56 the fruit list is "watermelon" doesn't have a area code. Please refer the above mentioned snapshot.

Match criteria: The matching selection criteria is "Fruit list" and "area code" in both tables.

Qty columns conditions; The qty columns shouldn't be blanks or 0. it's always greater than 1 expect the Banana with area code "PP1 and "KP1" its always equal to 1.

Sales code conditions; The sales code columns shouldn't be blanks or 0.

File attached for your reference. https://www.dropbox.com/scl/fi/8b4cnamqzsk2rannvowkk/POER-BI-DATA-SOF.xlsx?dl=0&rlkey=zt7y1rg2di1b53r6ta2kb3jj0

Answer 1

Thanks for including sufficient detail in your question.

• You can try the code below, it gives me your expected output. I don't know how performant this code will be if you're working with a lot of rows since I can't test it. It might better to use Table.NestedJoin instead of Table.Contains, although Table.Contains probably stops checking once a match has been found, which is better in this scenario.

• I don't think you said anything in your question about checking whether the value for SALES CODE in TABLE2 is in any of the values for columns SALES CODE 1, SALES CODE 2, SALES CODE 3 in TABLE1. I think that's what this bit SUMPRODUCT((TABLE1!B$2:B$18=C2)*(TABLE1!E$2:G$18=F2))>0 in your Excel formula is doing.

• Obviously, you will need to replace table1 and table2 in the code below with your own code/data, as mine are just hard coded examples.

• I recommend that you try it with as much real data as you can just to ensure that the code works and that the STATUS column always contains the value you expect it to contain.

• You can turn the TRUE/FALSE values into YES/NO with Table.TransformColumns. I've kept them as TRUE/FALSE for now.

---

let
    table1 = Table.FromRows({
        {"Avocado", "3100", 1, "EN", "EU01", "US", "UK"},
        {"Avocado", "3200", 1, "EN", "EU01", "US", "UK"},
        {"Avocado", "3300", 1, "EN", "EU01", "US", "UK"},
        {"Avocado", "4500", 1, "EN", "EU01", "US", "UK"},
        {"Avocado", "8900", 1, "EN", "EU01", "US", "UK"},
        {"Star Fruit", "WEST", 1, "EN", "INDIA01", "INDIA02", "NA"},
        {"Star Fruit", "NORTH", 1, "EN", "INDIA01", "INDIA02", "NA"},
        {"Star Fruit", "EAST", 1, "EN", "INDIA01", "INDIA02", "NA"},
        {"Star Fruit", "SOUTH", 1, "EN", "INDIA01", "INDIA02", "NA"},
        {"Banana", "KP1", 1, "EN", "XX1", "NA", "NA"},
        {"Banana", "PP1", 1, "EN", "XX2", "NA", "NA"},
        {"Apple", "444", 1, "EN", "BAN03", "NA", "NA"},
        {"Orange", "555", 1, "EN", "BAN03", "NA", "NA"},
        {"Orange", "555", 1, "EN", "BAN04", "NA", "NA"},
        {"Orange", "555", 1, "EN", "BAN05", "NA", "NA"},
        {"Plum", "123", 1, "EN", "BAN03", "NA", "NA"},
        {"Watermelon", "", 1, "EN", "EU01", "US", "UK"}
    }, type table [FRUIT LIST = text, AREA CODE = text, QTY = Int64.Type, LANGUAGE = text, SALES CODE 1 = text, SALES CODE 2 = text, SALES CODE 3 = text]),

    table2 = Table.FromRows({
        {"123", "Avocado", "3100", 100, "EN", "EU01"},
        {"124", "Avocado", "3100", 100, "EN", "US"},
        {"125", "Avocado", "3100", 100, "EN", "UK"},
        {"126", "Avocado", "WEST", 100, "EN", "UK"},
        {"127", "Avocado", "3100", 1, "EN", "UK"},
        {"128", "Avocado", "3100", null, "EN", "UK"},
        {"129", "Avocado", "3100", 100, " ", "UK"},
        {"130", "Avocado", "3100", 100, "EN", "INDIA01"},
        {"131", "Avocado", "3100", 100, "EN", ""},
        {"234", "Star Fruit", "WEST", 200, "EN", "INDIA01"},
        {"235", "Star Fruit", "NORTH", 100, "EN", "INDIA01"},
        {"236", "Star Fruit", "EAST", 43, "EN", "INDIA01"},
        {"237", "Star Fruit", "SOUTH", 56, "EN", "INDIA01"},
        {"238", "Star Fruit", "WEST", 78, "EN", "INDIA02"},
        {"239", "Star Fruit", "NORTH", 99, "EN", "INDIA02"},
        {"240", "Star Fruit", "EAST", 133, "EN", "INDIA02"},
        {"241", "Star Fruit", "SOUTH", 45, "EN", "INDIA02"},
        {"56", "Watermelon", "", 100, "EN", "UK"}
    }, type table [ITEM = text, FRUIT LIST = text, AREA CODE = text, QTY = Int64.Type, LANGUAGE = text, SALES CODE = text]),

    statusColumn = Table.AddColumn(table2, "STATUS", (currentRow) =>

        // Check quantity first since it should be a quick/cheap comparison, especially with short-circuit evaluation.
        currentRow[QTY] <> null and (if currentRow[FRUIT LIST] = "Banana" then currentRow[QTY] = 1 else currentRow[QTY] > 1)

        // FRUIT LIST && AREA CODE matches any TABLE1 row?
        and Table.Contains(table1, currentRow[[FRUIT LIST], [AREA CODE]], {"FRUIT LIST", "AREA CODE"})

        // AREA CODE && LANGUAGE matches any TABLE1 row?
        and Table.Contains(table1, currentRow[[AREA CODE], [LANGUAGE]], {"AREA CODE", "LANGUAGE"})

        // AREA CODE && SALES CODE matches any TABLE1 row?
        and Table.Contains(table1, currentRow, (table1row as record, currentRow as record) =>
        
            table1row[AREA CODE] = currentRow[AREA CODE]
            
            // SALES CODE needs custom treatment as there is no SALES CODE column in TABLE1
            and List.Contains(Record.FieldValues(table1row[[SALES CODE 1], [SALES CODE 2], [SALES CODE 3]]), currentRow[SALES CODE])
        )

    , type logical)

in
    statusColumn