Power Query Merge with multiple criteria

[Sohail-Merchant]

I have two tables, Sales and Payment. Both table have four columns. Sample is shown below: 

Sales

Transaction Date	Amount	Bank	Store
2/1/2020	3886	A	ANC
2/1/2020	1163	A	ANC
2/1/2020	3879	A	ANC
2/1/2020	7058	A	ANC
2/1/2020	688	A	ANC
2/1/2020	3965	A	ANC
2/1/2020	16988	A	ANC
2/1/2020	175	A	ANC
2/1/2020	5018	A	ANC
2/1/2020	1771	A	ANC

Payment

Transaction Date	Amount	Bank	Store
2/1/2020	25733.45	A	ANC
2/1/2020	839	A	ANC
2/1/2020	2827.78	A	ANC
2/1/2020	646	A	ANC
2/1/2020	1942	A	ANC
2/1/2020	1913.72	A	ANC
2/1/2020	5064	A	ANC
2/1/2020	1450	A	ANC

I want to merge both tables, based on following 11 criteria

----------------------------

1 Match sales sheet Amount column with Payment sheet amount column

For Unmatch Transactions

2 Than subtract -1 in unmatch transactions and match with payment sheet amount column

3 Than subtract -2 in unmatch transactions and match with payment sheet amount column

4 Than subtract -3 in unmatch transactions and match with payment sheet amount column

5 Than subtract -4 in unmatch transactions and match with payment sheet amount column

6 Than subtract -5 in unmatch transactions and match with payment sheet amount column

7 Than subtract +1 in unmatch transactions and match with payment sheet amount column

8 Than subtract +2 in unmatch transactions and match with payment sheet amount column

9 Than subtract +3 in unmatch transactions and match with payment sheet amount column

10 Than subtract +4 in unmatch transactions and match with payment sheet amount column

11 Than subtract +5 in unmatch transactions and match with payment sheet amount column

----------------------------

[Mariusz]

Hi @Sohail-Merchant 

Would you mind to produce a table with the expected result?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Sohail-Merchant]

Single Separate table, satisfying all the condition is perfect. However, you can provide any other solutions to get the merge data according to the mention criteria.

[Anonymous]

it looks like you're looking for something like this:

= Table.SelectRows(sales, each List.Contains(payment[Amount],_[Amount],(x,y)=>Number.Abs(x-y)<=5))

but it is only an attempt to interpret what you have written, because it is not clear at all to me.

if you want a possibly quick and useful answer, it would be preferable for you to provide examples of  input and expected output.