Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All,
I have the following problem (tables are simplified):
Table Invoices:
InvoiceID | PhaseCode | Instalment | DueDate | Amount |
101 | CD | A0 | 02/10/2021 | 1000 |
101 | CD | P0 | 02/15/2021 | 4000 |
101 | CD | P15 | 03/31/2021 | 5000 |
Table Payments
InvoiceID | PhaseCode | PaymentDate | Amount |
101 | CD | 02/25/2021 | 500 |
101 | CD | 03/03/2021 | 1500 |
101 | CD | 03/10/2021 | 1000 |
101 | CD | 03/15/2021 | 6500 |
So Payments table doesn't have Instalment code specified.
One payment can partially settle an invoice or it can settle one or several invoices.
How can I build the following output table in Power Query?
Table Output
InvoiceID | PhaseCode | Instalment | PaymentDate | Amount |
101 | CD | A0 | 02/25/2021 | 500 |
101 | CD | A0 | 03/03/2021 | 500 |
101 | CD | P0 | 03/03/2021 | 1000 |
101 | CD | P0 | 03/10/2021 | 1000 |
101 | CD | P0 | 03/15/2021 | 2000 |
101 | CD | P15 | 03/15/2021 | 4500 |
I appreciate any help you can offer.
Thank you!
Solved! Go to Solution.
Hello everybody,
do you know if there is a way to consider only the payments which have an invoice number ID similar to the invoice?
In this example i don't want to consider the payment with invoice "1033" which is not in the invoice list.
Thank you so much
Thank you for the new solution!
I had some busy days at work. I hope I will try it this weekend.
The invoices and payments tables have about 50 records now, for testing only. We will start adding real data the following weeks.
I'll compare the solutions when we have more data and post the results here.
@Anonymous
Thank you for your time and for the solution.
Is there a way to make List.Accumulate function to also include Instalment Codes in the result?
It is not reliable to join tables by Amount column.
Ok.
I think I understand the meaning of observation.
I think the best solution is to add the use of the Instalment variable in the IadoP function. If you can't do it, when I have time, I'll do it. I don't know if you need to apply this code to very large tables. I'm afraid it's not very performing.
But the rules to be implemented are really very very complex, for a language like M.
I tried to change your code to include Instalment Codes, but couldn't figure it out so far. I am still trying to understand list functions 🙂
I am quite new to PowerBI and PowerQuery, less than one year, but determined to learn 🙂
The project is ideed complex, with lots of tables from different sources, fortunately tables are not very large and we don't expect them to grow much (most of the tables have less than 300 rows).
I almost completed it, this payments splitting problem is the only one I couldn't figure out by myself.
Thanks again for your time and involvement.
To make the solution more general and robust, you should better explain the logic with which payments are divided between the different invoices.
Why, for example, is the payment of March 15th split between instalment P0 (with 2000) and P15 (with 4500)?
And why is the payment of March 3 split between instalment A0 wiyth 500 and P0 with 1000?
@Anonymous
Thank you for your answer.
I think I simplified the tabels too much.
The Invoices table also have a DueDate column, I edited my post and added the column.
Since Payments table doesn't have an Instalment reference, I have to allocate payments to Instalments in chronological order:
A0 (1000), has first due date and it should have the following payments allocated:
P0 (4000) has the second due date and the following payments:
P15 (5000) has the last due date and is payed partially in March 15th (4500 unallocated payment) and rest of 500 remains unpayed.
In the end, the Output table should show when and how each instalment was payed
Thanks again for your involvement!