Conditional merge in Power query Excel

[Flixg]

Hello there,  I need help with the following issue *URGENT*

Here is the situation I'm in - there are 2 data tables:

1. Table one: discount table, looks like this: 

L_bound	U_bound	%Discount
1	9	0
10	99	0.1
100	199	0.2
200	299	0.3

1. Table two: shipments table, looks like this:

Shipmnt_ID	prod	Cust_ID	Cust_ttl_SHipmnt	Shipmnst_cost
1	monitor	Danny	50	1000
2	Computer	Tomas	9	500
2	.....	.....	10	2000
4	-------	------	150	10000

I need to add the relevant “%Discount” from table one to every shipment in table two based on “Cust_ttl_SHipmnt” value.

For example, for the first shipment the relevant “%Discount” is  0.1 (Because 10< Cust_ttl_SHipmnt <99)

The result of the merge I need, looks like the following table:

Shipmnt_ID	prod	Cust_ID	Cust_ttl_SHipmnt	Shipmnst_cost	%Discount
1	monitor	Danny	50	1000	0.1
2	Computer	Tomas	9	500	0
2	.....	.....	10	2000	0.1
4	-------	------	150	10000	0.2

[v-jingzhang · Accepted solution]

Hi @Flixg 

I have provided a solution in your earlier post. Please find it there Conditional merge in Power query Excel - Microsoft Power BI Community

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.