Forum Discussion
My queries are running so slowly!
Community ChampionHi Anonymous ,
yes, that makes sense now: Your running total needs to go through the whole table and has to store each step in there. As you are trying to create a grouped/clustered running total, it would be much better if you would group your table on [CaseNo] and apply the running total function on the resulting partitions. I've described that procedure here:
Memory efficient clustered running total in Power BI – The BIccountant
If your data isn't sorted by CaseNo already, you must omitt the change to "GroupKind.Local".
It's defintiely running faster (thank you!!), but now I'm getting a couple of errors and I can't figure out how to fix them.
1. It's saying the column 'Sec Total Avg SEI' wasn't found. It's in the source table, and if I was to manually expand the columns, it's in the expandable columns too. Do I need to expand all the columns that I need?
2. The running total column just errors. I think I may have the wrong field selected within the code!
fxSecRunningTotal
let
func = (Table as table, SortColumn as text, AmountColumn as text) =>
let
/* Debug parameters
Table = #"4c - Sec Data Table",
SortColumn = "CaseNo",
AmountColumn = "Sec Avg Activity SEI",
*/
// Sort table and buffer it
Sorted = Table.Buffer(Table.AddIndexColumn(Table.Sort(Table,{{SortColumn, Order.Ascending}}), "Index",1,1)),
// Select the Columns
SelectColumns = Table.SelectColumns(Sorted, {SortColumn, AmountColumn, "Index"}),
// Extract Amount column and buffer it
ExtractAmountColumn = List.Buffer(Table.Column(SelectColumns, AmountColumn)),
// Calculate a list with all running Totals
RunningTotal = List.Skip(List.Generate( ()=> [ListItem=0, Counter=0]
,each [Counter] <= List.Count(ExtractAmountColumn)
,each [ ListItem = ExtractAmountColumn{[Counter]}+[ListItem],
Counter = [Counter]+1
]
),1),
ConvertedTable = Table.FromList(RunningTotal, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedColumn = Table.ExpandRecordColumn( ConvertedTable, "Column1", {"ListItem", "Counter"}, {"ListItem", "Counter"}),
MergedQueries = Table.NestedJoin(Sorted,{"Index"}, ExpandedColumn,{"Counter"},"Expanded Column1",JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn( MergedQueries, "Expanded Column1", {"ListItem"}, {"RunningTotal"}),
#"Removed Columns" = Table.RemoveColumns(Expand,{"Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"RunningTotal", type number}})
in
#"Changed Type"3c – SecOverUnder Index
let
Source = #"1 - Data Table",
#"Buffered Table" = Table.Buffer(Source),
//Running Total
#"Group by CaseNo" = Table.Group(#"Buffered Table", {"CaseNo"}, {{"All", each _, type table}}, GroupKind.Local),
#"Added Running Total 1" = Table.AddColumn(#"Group by CaseNo","Sec Avg Running Total", each fxSecRunningTotal([All], "CaseNo", "Sec Avg Activity SEI")),
//Calculate over/under SEI
#"Added Conditional Column" = Table.AddColumn(#"Added Running Total 1", "Sec OverUnder", each if [Sec Avg Running Total] > [Sec SEI Value] then "Over" else if [Sec Avg Running Total] < [Sec SEI Value] then "Under" else "Equal"),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Sec Total Avg SEI", type number}, {"Sec Avg Running Total", type number}}),
#"Sec Avg Running Total" = #"Changed Type"{0}[Sec Avg Running Total]
in
#"Sec Avg Running Total"