Aggregate table

[omega123]

Hi all.
I would like to get one aggregate table from the billing and incoming payments table, where the incoming payments should be assigned to the accounts with the earliest due dates (see 2 tables below)
For example, a payment of 400 under contract 2 on 25.02.2022 must first cover the amount of 300 from 08.02.2022 with a due date of 22.02.2022, and then the amount of 200 from 19.02.2022 with a due date of 25.03.2022. Thus, Contract 2 will have 100 left to pay (and that amount will be listed on the line with a due date of 25.03.2022).
Similarly, the payment on 26.02.2022 under contract 1 will cover the earliest amount of 100 with a due date of 15.02.2022, then part of the amount of 500 with a due date of 07.03.2022, and so on.
I am new to Power Query and don't understand how to solve this problem yet and how to get the aggregate table.
I would be grateful for your tips.

INITIAL TABLE

Date	Contract nmb	Amount payable	Payment deadline	Received payment
01.02.2022
02.02.2022	1	100	15.02.2022
03.02.2022	3	200	15.02.2022
04.02.2022
05.02.2022	4	400	19.02.2022
06.02.2022
07.02.2022
08.02.2022	2	300	22.02.2022
09.02.2022
10.02.2022	5	100	24.02.2022
11.02.2022
12.02.2022
13.02.2022	1	500	07.03.2022
14.02.2022	1	400	10.03.2022
15.02.2022
16.02.2022	3	100	17.03.2022
17.02.2022	5	800	20.03.2022
18.02.2022
19.02.2022	2	200	25.03.2022
20.02.2022
21.02.2022
22.02.2022
23.02.2022
24.02.2022
25.02.2022	2			400
26.02.2022	1			300
27.02.2022	1			100

AGGREGATE TABLE

Date	Contract nmb	Amount payable	Payment deadline	Received payment	Remaining amount payable
02.02.2022	1	100	15.02.2022	100	0
03.02.2022	3	200	15.02.2022		200
05.02.2022	4	400	19.02.2022		400
08.02.2022	2	300	22.02.2022	300	0
10.02.2022	5	100	24.02.2022		100
13.02.2022	1	500	07.03.2022	300	200
14.02.2022	1	400	10.03.2022		400
16.02.2022	3	100	17.03.2022		100
17.02.2022	5	800	20.03.2022		800
19.02.2022	2	200	25.03.2022	100	100

[omega123]

Hi @v-jingzhang 

thanks for the helpful solution! It works well. I was also able to create a few more queries of my own.

One more question:
When I click Save and Close in Power Query, all but one of the tables I created in Power Query are successfully loaded into Excel. Excel reports that it was unable to restore the data in that one table. How can I still import it from Power Query into Excel? What could be the problem here and what could be possible solutions?

[v-jingzhang · Accepted solution]

Hi @omega123 

I have created a sample. You can see detailed transformation steps in the sample .pbix file attached at bottom. 

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