Power Platform Community Forum Thread Details

Power Query Merge based on conditions

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	<blank>
1236	Green bike	XYZ Co.	New York	NY	<blank>
1237	Blue bike	GHI Co.	Cleveland	OH	W1
1238	Red bike	DEF Co.	Taylor	MI	W3
1239	Green bike	GHI Co.	New York	NY	<blank>