Forum Discussion
Alternate for Table.Group / Table.AggregateTableColumn, Slow power query data loading
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.
Hi CMMaliniJoshi ,
there are quite a couple of things here that you can try to improve performance:
- 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.
- 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!!
- 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.
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_SumHowever, 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
