Power Query Merge based on conditions

[Syndicate_Admin]

Hi everyone,

I am trying to merge two tables, and I think I need to do the merge based on conditions.  The first table is a list of all finished inventory, which includes the end customer name, city and state.  The second table is a list of customers and which warehouse each should go to.  I want to use Power Query to add the warehouse to the inventory listing.  (I am currently using an Excel formula, but the inventory list is long and performance is very poor.)  The issue is that our criteria for sending material to a warehouse can vary by customer, as shown in the image below.  How can I set up a merge with the following conditions?

- If the customer list specifies the customer name only (city shows 'all' and state shows 'all') then merge based on the customer name only

- If the customer list specifies the customer name and state (and city shows 'all'), then merge based on the customer name and state

- If the customer list specifies the customer name, city, and state, then merge based on all 3 fields

Customer	City	State	Warehouse
ABC Co.	Denver	CO	W1
ABC Co.	Boulder	CO	W2
DEF Co.	all	all	W3
GHI Co.	all	OH	W1
GHI Co.	all	WA	W2

Desired Result:

Serial No	Product	Customer	City	State	Warehouse
1234	Blue bike	ABC Co.	Denver	CO	W1
1235	Red bike	ABC Co.	Detroit	MI
1236	Green bike	XYZ Co.	New York	NY
1237	Blue bike	GHI Co.	Cleveland	OH	W1
1238	Red bike	DEF Co.	Taylor	MI	W3
1239	Green bike	GHI Co.	New York	NY

All help appreciated!

[wdx223_Daniel]

let
    Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVnDO11PSUXJJzStLLQIynP2BRLihUqwOsrRTfmlOCpK8EVjexdUNKp+YkwMnw43Bku4eniiS/h4Ig9Hlwh2hhsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, City = _t, State = _t, Warehouse = _t]),
    Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUXLKKU1VSMrMTgWyHZ2cFZzz9YAsl9S8stQiIMPZXylWB6zYFMgLSk3BprakKD+zBMjy9YQpNgPy3ItSU/NgyiMio6DK/VLLFSLzi7JBzEiYenM0l7h7eEKVO+eklqXmJOalANn+HjD1FqiOcXF1gyoPSazMyS9CcYslulsQhqO5JRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial No" = _t, Product = _t, Customer = _t, City = _t, State = _t]),
    Custom1=let a=Table.Buffer(Source1) in Table.AddColumn(Source2,"Warehouse",each let w1=a{[Customer=[Customer],State=[State],City=[City]]}?[Warehouse]?,w2=a{[Customer=[Customer],State=[State],City="all"]}?[Warehouse]?,w3=a{[Customer=[Customer],State="all",City="all"]}?[Warehouse]? in w1??w2??w3)
in
    Custom1

[AlienSx]

Hello, Barbara. I am not sure about performance but give this a try

let
    customer = your_customers_table,
    inventory = your_inventory_table,
    cust_group = Table.Group(customer, {"Customer"}, {{"all", Table.ToRecords}}),
    cust_dict = Record.FromList(cust_group[all], cust_group[Customer]),
    fx_select = (dict, rec) =>
        [a = Record.FieldOrDefault(dict, rec[Customer], null),
        sel = if a = null then null else 
            List.Select(
                a, 
                (x) => 
                    (x[City] = "all" or x[City] = rec[City]) and 
                    (x[State] = "all" or x[State] = rec[State])
            ){0}?[Warehouse]?][sel],
    wh = Table.AddColumn(inventory, "Warehouse", (x) => fx_select(cust_dict, x))
in
    wh

[MarkLaf]

1) Split your second table into three queries where each is filtered by your conditions (i.e., one table has customer, all, all; another has customer, state, all; etc.). Disable load on these tables

2) Now that you have a table for each scenario, you can do the appropriate correspoding merge. You can set the key(s) or remove duplicates to speed up merge.

3) Once joined, you can combine (append) the tables back together. This gives you all the invetory rows with warehouses. Remove warehouse rows that don't have any corresponding inventory. Don't load

4) use an antijoin with original Inventory table with #3 to get all your inventory rows without warehouses. Don't load

5) Combine (append) tables from #3 and #4 to get your complete InventoryWarehouses table. Load this table

The flow of all this will look like:

Queries:

CustomerWarehouses:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVnDO11PSUXJJzStLLQIynP2BRLihUqwOsrRTfmlOCpK8EVjexdUNKp+YkwMnw43Bku4eniiS/h4Ig9Hlwh2hhsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, City = _t, State = _t, Warehouse = _t])
in
    Source

Inventory:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUXLKKU1VSMrMTgWyHZ2cFZzz9YAsl9S8stQiIMPZXylWB6zYFMgLSk3BprakKD+zBMjy9YQpNgPy3ItSU/NgyiMio6DK/VLLFSLzi7JBzEiYenM0l7h7eEKVO+eklqXmJOalANn+HjD1FqiOcXF1gyoPSazMyS9CcYslulsQhqO5JRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial No" = _t, Product = _t, Customer = _t, City = _t, State = _t])
in
    Source

CustomerAllAll:
let
    Source = CustomerWarehouses,
    Filter = Table.SelectRows(Source, each ([State] = "all") and ([City] = "all")),
    Distinct = Table.Distinct(Filter, {"Customer"}),
    MergeInv = Table.NestedJoin(Distinct, {"Customer"}, Inventory, {"Customer"}, "Inventory", JoinKind.LeftOuter)
in
    MergeInv

CustomerStateAll:
let
    Source = CustomerWarehouses,
    Filter = Table.SelectRows(Source, each ([City] = "all") and ([State] <> "all")),
    Distinct = Table.Distinct(Filter, {"Customer", "State"}),
    MergeInv = Table.NestedJoin(Distinct, {"Customer", "State"}, Inventory, {"Customer", "State"}, "Inventory", JoinKind.LeftOuter)
in
    MergeInv

CustomerStateCity:
let
    Source = CustomerWarehouses,
    Filter = Table.SelectRows(Source, each ([State] <> "all") and ([City] <> "all")),
    Distinct = Table.Distinct(Filter, {"Customer", "City", "State"}),
    MergeInv = Table.NestedJoin(Distinct, {"Customer", "State", "City"}, Inventory, {"Customer", "State", "City"}, "Inventory", JoinKind.LeftOuter)
in
    MergeInv

InventoryWithWarehouses:
let
    Source = Table.Combine({CustomerStateCity, CustomerStateAll, CustomerAllAll}),
    RemoveWHCols = Table.RemoveColumns(Source,{"Customer", "City", "State"}),
    ExpandInv = Table.ExpandTableColumn(RemoveWHCols, "Inventory", {"Serial No", "Product", "Customer", "City", "State"}, {"Serial No", "Product", "Customer", "City", "State"}),
    RemoveEmptyWH = Table.SelectRows(ExpandInv, each ([Customer] <> null))
in
    RemoveEmptyWH

InventoryNoWarehouses:
let
    Source = Table.NestedJoin(InventoryWithWarehouses, {"Serial No", "Product", "Customer", "City", "State"}, Inventory, {"Serial No", "Product", "Customer", "City", "State"}, "Inventory", JoinKind.RightAnti),
    RemoveWHCols = Table.RemoveColumns(Source,{"Warehouse", "Serial No", "Product", "Customer", "City", "State"}),
    ExpandInv = Table.ExpandTableColumn(RemoveWHCols, "Inventory", {"Serial No", "Product", "Customer", "City", "State"}, {"Serial No", "Product", "Customer", "City", "State"})
in
    ExpandInv

InventoryWarehouses:
let
    Source = Table.Combine({InventoryWithWarehouses, InventoryNoWarehouses})
in
    Source

Result: