Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
----------------------------
Would you mind to produce a table with the expected result?
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |