Alternate for Table.Group / Table.AggregateTableColumn, Slow power query data loading

[CMMaliniJoshi]

Dear All

I have created a Query based on below mentioned data

Ac_no	Maturity_Amount	Maturity_Date
1	388211	25.09.2023

I have to calculate Interest on Maturity amount as per below mentioned parameters:

Date_Upto	Interest_Rate
31-Aug-2017	4%
30-Jun-2018	3.5%
14-Oct-2023	3%

Explanation : Based on maturity date, I have to calculate interest @4% upto 31-08-2017, 3.5% upto 30-06-2018 and 3% upto 14-10-2023. If maturity date is after 14-10-2023, no interest to be given. If maturity date is 01-Jun-2018, No interest to be given for first parameter i.e. 31-Aug-2017.

I have tried to prepare a query in which I have reached upto the point of calculating per day interest.  Query prepared is as under:

let
Source = Excel.CurrentWorkbook(){[Name="fData_Table"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Maturity_Date", type date}}),
#"Added Custom" = Table.Buffer(Table.AddColumn(#"Changed Type", "Day", each {Number.From([Maturity_Date])..Number.From(List.Max(dROI[Date_Upto]))})),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Day"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Day", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "ROI", (X) => Table.Min(Table.SelectRows(dROI, (Y) => X[Day] <= Y[Date_Upto]),"Date_Upto")[ROI]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day_Interest", each Number.Round([Maturity_Amount]*[ROI]/365,2),type number)
in
#"Added Custom2"

Table named dROI contains all 3 Interest Rate parameters and fData_Table contains data of all deposits three columns as mentioned earlier (Ac_No, Maturity Amount, Maturity Date)

After the Step - #"Added Custom2", I am getting output as under:

Ac_no	Maturity_Amount	Maturity_Date	Day	ROI	Day_Interest
1	388211	25.09.2023	25.09.2023	0.03	31.91

Hereafter, I tried to group on Ac_No. to derive at total interest for particular account with the help of Table.Group.  But my computer stopped working.  I also tried to create another query with only account numbers and then tried to merge it with above query. Then I tried to use Table.AggregateTableColumns to get Total of Day_Inerest column coming from Merged Query.  But that also resulted in my computer stopped working.  When I tried to load the data as appearing at Step Named  #"Added Custom2" to data model, my data load was very much slow. It took about 50 minutes to load data as total rows are around 5453869.  My basic data is of 2500 rows only, but above transformations / steps have resulted in 5453869 rows.  This much of data has probably resulted in stopping my computer /slowing down my computer.  I request all to guide on below:

1. Why power query is not able to work on Table.Group and Table.AggregateTableColumn
2. Why data loading to data model is very much slow for 5453869 rows.
3. What is alternate solution for above scenario.

My final desired output is as under:

Ac_No	Maturity_Amount	Maturity_Date	31-08-2017	30-06-2018	14-10-2023
1	388211	25.Sep.23	Amt1	Amt2	Amt3

In above table, the three date columns need to show total interest payable upto that particular date mentioned in column name.

Kindly help on above mentioned 3 points

Regards

[ImkeF · Accepted solution]

Hi @CMMaliniJoshi ,
just saw that you have an unbuffered reference to the table here still - that might cause the slow performance:
Added_Custom = Table.AddColumn(Changed_Type, "Day", each {Number.From([Maturity_Date])..Number.From(List.Max(dROI[Date_Upto]))}),

Cannot do any more here. Further help would need the data itself, but for me, this would then run into consultancy services that I would have to charge for.
You might want to consider opening separate threads for it and make sure to include sample data to it so folks can follow the desired logic.

[CMMaliniJoshi]

Dear ImkeF

Thanks a lot....

REGARDS

[ImkeF]

Hi @CMMaliniJoshi ,
there are quite a couple of things here that you can try to improve performance:

1. Remove the Table.Buffer in the "Added Column"-step. It doesn't improve any further steps and given the explosion of rows that you are creating in this step, will severely slow down the refresh.
2. Make sure that the table that you reference in each row of step Added Custom1 actually is buffered (table dROI). This is crucial, as you are going to reference it 5 Mio times!!
3. Consider transforming part of your query logic into a function so that you can do everything on the level of your original table: Basically starting from the "Added Custom"-step where you explode your rows up until the aggregation. Advantage will be that you don't have to group, but instead can just do a simple List.Sum because you are on the level on one AcNo only.

[CMMaliniJoshi]

Dear Imke

I am sorry for the late revert. I was not in good health.  I am thankfull for taking interest in my query.  Kindly note that -

A.  The two suggestions / advices given by you in point no. 1 and 2 are really useful.  They have resulted in my query started working whereas eaerlier my computer stopped working.

B.  I have tried to further my logic to achieve what I intended and I have reached somewhat near to my desired output.  At present my PQ Query looks as under:

let
Source = Excel.CurrentWorkbook(){[Name="fData_Table"]}[Content],
Changed_Type = Table.TransformColumnTypes(Source,{{"Maturity_Date", type date}}),
Added_Custom = Table.AddColumn(Changed_Type, "Day", each {Number.From([Maturity_Date])..Number.From(List.Max(dROI[Date_Upto]))}),
Expanded_Custom = Table.ExpandListColumn(Added_Custom, "Day"),
Changed_Type1 = Table.TransformColumnTypes(Expanded_Custom,{{"Day", type date}}),
ROI_Buffer = Table.Buffer(dROI),
Added_Custom1 = Table.AddColumn(Changed_Type1, "ROI_Date", (X) => Table.Min(Table.SelectRows(ROI_Buffer, (Y) => X[Day] <= Y[Date_Upto]),"Date_Upto")[[ROI],[Date_Upto]]),
Expanded_ROI = Table.ExpandRecordColumn(Added_Custom1, "ROI_Date", {"ROI", "Date_Upto"}, {"ROI", "Date_Upto"}),
Changed_Type2 = Table.TransformColumnTypes(Expanded_ROI,{{"ROI", type number}, {"Date_Upto", type date}, {"Ac_No", Int64.Type}}),
Added_Custom2 = Table.AddColumn(Changed_Type2, "Day_Interest", each Number.Round([Maturity_Amount]*[ROI]/365,2), type number),
Grouped_Rows = Table.Group(Added_Custom2, {"Ac_No", "Date_Upto"}, {{"Sum", each List.Sum([Day_Interest]), type number}}),
Pivoted_Column = Table.Pivot(Table.TransformColumnTypes(Grouped_Rows, {{"Date_Upto", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(Grouped_Rows, {{"Date_Upto", type text}}, "en-IN")[Date_Upto]), "Date_Upto", "Sum"),
Inserted_Sum = Table.AddColumn(Pivoted_Column, "Addition", each List.Sum(List.RemoveFirstN(Record.ToList(_),1)), type number)
in
Inserted_Sum

However, it still has following issues:

1. I am not getting Maturity Date and Maturity Amount in my Desired Output.

2. The sequence of dates in last step of Pivoted Column is incorrect. Secondly if the third parameter date in dROI table is earlier than the maximum date in my fDate_Table, then the query gives an error that it cannot covert null to a record. I am still trying solution for it.

2. The above query, eventhough working, is working terribly slow.  It still takes huge amount of time to reflect the result.  Therefore, I need to find some alternate logic to achieve the desired output fast.

C.  I am still working on the suggestion given by you in Point No. 3.  But since I am not very good in Power query / PQ programming, I am yet to figure out how to prepare a custome function which can give an output of 3 additional columns in the desired output.  Just now I am trying to use List.Accumulate / List.Generate to achieve desired output.

I shall be thankfull if you can help.

Regards