cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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}}),
#"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]),
in

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

1 ACCEPTED SOLUTION
Super User

Hi @CMMaliniJoshi ,
just saw that you have an unbuffered reference to the table here still - that might cause the slow performance:

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

4 REPLIES 4
Super User

Hi @CMMaliniJoshi ,
just saw that you have an unbuffered reference to the table here still - that might cause the slow performance:

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Helper I

Dear ImkeF

Thanks a lot....

REGARDS

Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Helper I

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}}),
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}}),
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"),
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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors