Problem with installments in Power Query

[lnrdflp]

Guys, I need helpdoing one thing on Power Query, and I already tried a lot of ways, but noneseems to do what I need.

I have an amount,and I need to plot a chart with the sum of all amounts, divide by the terms,during the period of the terms.

For instance, inNovember 2017 it would be $ 1500 (18000/12). In December 2017 it would be $7750 ($ 1500 2nd installment + $6250 1st installment).

The table with theamount, start date and period will be updated very often. I put an example below, but you can imagine the problem when there are thousands of registers, and hundreds of new ones every month.

Do you know a way toautomatically calculate the total amount for each month so I can plot a chart?

[TABLE="width: 1"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] "] Amount
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] "] StartDate
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] "] Term
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]

18000​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]

25/11/2017​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]

12​

[/TD]
[/TR]
[TR]
[TD]

50000​

[/TD]
[TD]

20/12/2017​

[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]

30000​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]

25/03/2018​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]

4​

[/TD]
[/TR]
[TR]
[TD]

36000​

[/TD]
[TD]

14/06/2018​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]

33000​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]

16/07/2018​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]

11​

[/TD]
[/TR]
[TR]
[TD]

60000​

[/TD]
[TD]

03/08/2018​

[/TD]
[TD]

10​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ron Coderre]

Assuming I interpreted your request properly...this was a fun challenge.


• Put your sample data into an Excel Table named Table1.
• Load that table to Power Query
Query Name: Table1

let
    Source       = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SetDataTypes = Table.TransformColumnTypes(Source,{{"Gross", Int64.Type}, {"StartDate", type date}, {"NumPmts", Int64.Type}})
in
    SetDataTypes

• Create a new query that references the Table1 Query
• Replace the default M-Code with this:
Query Name: CalcFlows

let
    Source = Table1,
    AddPmtMthLists = Table.AddColumn(Source, "MthRef", each
        List.Generate(
            ()=> [StartDate],
            (listval) => listval <= Date.AddMonths([StartDate],[NumPmts]-1),
            (listval) => Date.AddMonths(listval,1))),
    ExpandPmtMths = Table.ExpandListColumn(AddPmtMthLists, "MthRef"),
    SetMthDataType = Table.TransformColumnTypes(ExpandPmtMths,{{"MthRef", type date}}),
    AddPmtAmt = Table.AddColumn(SetMthDataType, "PmtAmt", each [Gross]/[NumPmts], type number),
    SortRowsByDate = Table.Sort(AddPmtAmt,{{"MthRef", Order.Ascending}}),
    AddIndex = Table.AddIndexColumn(SortRowsByDate, "Index", 1, 1),
    BuildCumulativeListDetails = Table.AddColumn(AddIndex, "Initial", each List.Range(AddIndex[PmtAmt],0,[Index])),
    CalcRunningTotals = Table.AddColumn(BuildCumulativeListDetails, "Running Total", each List.Sum(List.FirstN(AddIndex[PmtAmt],[Index]))),
    RemoveListCol = Table.RemoveColumns(CalcRunningTotals,{"Initial"})
in
    RemoveListCol

• These are the results:

Gross       StartDate    umPmts       MthRef         PmtAmt       Index  Running Total
18000       11/25/2017       12       11/25/2017       1500       1        1,500
50000       12/20/2017        8       12/20/2017       6250       2        7,750
18000       11/25/2017       12       12/25/2017       1500       3        9,250
50000       12/20/2017        8       01/20/2018       6250       4       15,500
18000       11/25/2017       12       01/25/2018       1500       5       17,000
50000       12/20/2017        8       02/20/2018       6250       6       23,250
18000       11/25/2017       12       02/25/2018       1500       7       24,750
50000       12/20/2017        8       03/20/2018       6250       8       31,000
30000       03/25/2018        4       03/25/2018       7500       9       38,500
18000       11/25/2017       12       03/25/2018       1500       10      40,000
50000       12/20/2017        8       04/20/2018       6250       11      46,250
18000       11/25/2017       12       04/25/2018       1500       12      47,750
30000       03/25/2018        4       04/25/2018       7500       13      55,250
50000       12/20/2017        8       05/20/2018       6250       14      61,500
18000       11/25/2017       12       05/25/2018       1500       15      63,000
30000       03/25/2018        4       05/25/2018       7500       16      70,500
36000       06/14/2018        5       06/14/2018       7200       17      77,700
50000       12/20/2017        8       06/20/2018       6250       18      83,950
18000       11/25/2017       12       06/25/2018       1500       19      85,450
30000       03/25/2018        4       06/25/2018       7500       20      92,950
36000       06/14/2018        5       07/14/2018       7200       21     100,150
33000       07/16/2018       11       07/16/2018       3000       22     103,150
50000       12/20/2017        8       07/20/2018       6250       23     109,400
18000       11/25/2017       12       07/25/2018       1500       24     110,900
60000       08/03/2018       10       08/03/2018       6000       25     116,900
36000       06/14/2018        5       08/14/2018       7200       26     124,100
33000       07/16/2018       11       08/16/2018       3000       27     127,100
18000       11/25/2017       12       08/25/2018       1500       28     128,600
60000       08/03/2018       10       09/03/2018       6000       29     134,600
36000       06/14/2018        5       09/14/2018       7200       30     141,800
33000       07/16/2018       11       09/16/2018       3000       31     144,800
18000       11/25/2017       12       09/25/2018       1500       32     146,300
60000       08/03/2018       10       10/03/2018       6000       33     152,300
36000       06/14/2018        5       10/14/2018       7200       34     159,500
33000       07/16/2018       11       10/16/2018       3000       35     162,500
18000       11/25/2017       12       10/25/2018       1500       36     164,000
60000       08/03/2018       10       11/03/2018       6000       37     170,000
33000       07/16/2018       11       11/16/2018       3000       38     173,000
60000       08/03/2018       10       12/03/2018       6000       39     179,000
33000       07/16/2018       11       12/16/2018       3000       40     182,000
60000       08/03/2018       10       01/03/2019       6000       41     188,000
33000       07/16/2018       11       01/16/2019       3000       42     191,000
60000       08/03/2018       10       02/03/2019       6000       43     197,000
33000       07/16/2018       11       02/16/2019       3000       44     200,000
60000       08/03/2018       10       03/03/2019       6000       45     206,000
33000       07/16/2018       11       03/16/2019       3000       46     209,000
60000       08/03/2018       10       04/03/2019       6000       47     215,000
33000       07/16/2018       11       04/16/2019       3000       48     218,000
60000       08/03/2018       10       05/03/2019       6000       49     224,000
33000       07/16/2018       11       05/16/2019       3000       50     227,000

Is that something you can work with?

 

[lnrdflp]

Thank you very much, Ron! It worked as I was hoping. The only problem is performance, since it took almost half an hour to calculate on a table with about 1700 lines. But since we are only going to calculate this once a month, it's not a big deal.

Really appreciate your support!

 

[Ron Coderre]

That seems like too long.
(Copied from one of my other posts)->
Try using these settings:
GLOBAL section
Data Load:
- Specify custom default load settings: UN-check the options
This is more for convenience...The default is to port the returned data immediately to a worksheet. If you aren't done with the query, you don't want that to happen. Also, most of my queries are retained as connections that pivot tables access directly. That avoids duplication of data.

- Check: Fast Data Load
Does what it says...speeds up the data. I believe it commits more PC resources to complete the query, but the the PC may become unresponsive for a few seconds. That's a price I gladly pay.

Privacy:
- Check: Always ignore Privacy Level settings
This refers to privacy levels you might set for your power queries. I've had no use for custom privacy levels. It has no impact on security set by your DBA's


CURRENT WORKBOOK section
Data Load:
- UN-check: Allow data preview to download in the background
When this is CHECKED...Power Query refreshes the 1000 record sample for every query in the workbook whenever ANY query is edited. BAD BAD BAD! You'd think it meant to only refresh the data preview for the current query, but: NO.
Also, the preview for the current query is unaffected by this setting...it will always update when edited.

I hope that helps.