My queries are running so slowly!

[Syndicate_Admin]

Hi Guys

Firstly, huge thanks with all the help you've provided so far. Piecing bits together from other answers has enabled me to get to this point, but now I'm stuck and I'm at my wits end. 

Below is the code I've created so far. And it works, it just takes so long to run (and that's with a much reduced sample file saved as a CSV file). I think it's at the BufferedCaseNo steps. I can't do this in DAX as I need the answer in this dataset to continue with the calculation, however I'm not certain as I'm still relatively new to Power Query. Any suggestions in where I can speed this up, would be greatly received.

Thanks!

let
    Source = #"1 - Data Table",
    
    //Add in total of the average SEI values for the case, for that division
    //#"Added Sec Tot Avg SEI" = Table.AddColumn(Source, "Sec Total Avg SEI", each let _item = [CaseNo] in List.Sum(Table.SelectRows(Source, each [CaseNo] = _item)[#"Sec Avg Activity SEI"])),
    #"Buffered Table" = Table.Buffer(Source),

    //Running Total
    BufferedCaseNo = List.Buffer(#"Buffered Table"[CaseNo]),
    BufferedValues = List.Buffer(#"Buffered Table"[Sec Avg Activity SEI]),
    RT = Table.FromList(fxSecRunningTotal(BufferedValues, BufferedCaseNo), Splitter.SplitByNothing(), {"RT"}),
    Columns = List.Combine({Table.ToColumns(#"Buffered Table"), Table.ToColumns(RT)}),
    #"Added Running Total 1" = Table.FromColumns(Columns, List.Combine({Table.ColumnNames(#"Buffered Table"), {"Sec Avg Running Total"}})),
    
    //Calculate over/under SEI
    #"Added Over/Under" = 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 Over/Under",{{"Sec Total Avg SEI", type number}, {"Sec Avg Running Total", type number}}),
    
    //Calculate remaining balance
    #"Calculate Remaining Balance" = Table.AddColumn(#"Changed Type", "Sec SEI Balance", each [Sec SEI Value] - [Sec Avg Running Total], type number),
    
    //Calculate Final Activity and display in new column
    #"Max Activity Find" = Table.NestedJoin(#"Calculate Remaining Balance", {"CaseNo"}, #"2 - Max Activity Table", {"CaseNo"}, "Max Activity Table", JoinKind.LeftOuter),
    #"Max Activity Display" = Table.ExpandTableColumn(#"Max Activity Find", "Max Activity Table", {"MaxActivity"}, {"MaxActivity"}),
    
    //Max Activity additional SEI value
    //#"Added Conditional Column1" = Table.AddColumn(#"Max Activity Display", "Max Act SEI Value Add", each if [MaxActivity] = [Activity Order] and [Ops OverUnder] = "Under" then [Ops SEI Balance] else if [MaxActivity] = [Activity Order] and [Ops OverUnder] = "Over" then [Ops SEI Balance] else 0)
    
    //New Running Total
    BufferedCaseNo2 = List.Buffer(#"Max Activity Display"[CaseNo]),
    BufferedValues2 = List.Buffer(#"Max Activity Display"[Sec Avg Activity SEI]),
    RT2 = Table.FromList(fxSecRunningTotal(BufferedValues, BufferedCaseNo), Splitter.SplitByNothing(), {"RT2"}),
    Columns2 = List.Combine({Table.ToColumns(#"Max Activity Display"), Table.ToColumns(RT2)}),
    #"Added Running Total 2" = Table.FromColumns(Columns2, List.Combine({Table.ColumnNames(#"Max Activity Display"), {"Sec Avg Running Total2"}})),
    #"Calculate additional value for max activity" = Table.AddColumn(#"Added Running Total 2", "Sec Over SEI Value", each if [Sec OverUnder] = "Over" then ([Sec Avg Running Total2] + [Sec SEI Balance]) - [Sec Avg Activity SEI] else 0),
    
    //Indexes activities within a case and whether they are over or under the SEI for the case. This is to establish additional apportionment values
    #"Added Index to Case and Over/Under" = Table.NestedJoin(#"Calculate additional value for max activity", {"CaseNoActOrderID"}, #"3c - SecOverUnder Index", {"Custom.CaseNoActOrderID"}, "5c - SecOverUnder Index", JoinKind.LeftOuter),
    #"Expanded 5c - SecOverUnder Index" = Table.ExpandTableColumn(#"Added Index to Case and Over/Under", "5c - SecOverUnder Index", {"Custom.Index1"}, {"Sec OverUnder Index"}),
    
    //Ops SEI Value if running total is greater than the total SEI for that case
    #"Handling of SEI when running total is greater than total value" = Table.AddColumn(#"Expanded 5c - SecOverUnder Index", "Custom", each if [Sec OverUnder] = "Over" and [Sec OverUnder Index] = 1 then [Sec Avg Activity SEI] + [Sec SEI Balance] else 0),
    #"Renamed Columns1" = Table.RenameColumns(#"Handling of SEI when running total is greater than total value",{{"Custom", "Sec Over SEI New Value"}}),
    
    //Ops SEI value is under and it's the final activity
    #"Handling of final activity if running total is under total value" = Table.AddColumn(#"Renamed Columns1", "Custom", each if [Sec OverUnder] = "Under" and [Activity Order] = [MaxActivity] then [Sec Avg Activity SEI] + [Sec SEI Balance] else 0),
    #"Renamed Columns2" = Table.RenameColumns(#"Handling of final activity if running total is under total value",{{"Custom", "Sec Under SEI New Value"}}),
    
    //Final Sec SEI Value
    #"Add Sec Final SEI" = Table.AddColumn(#"Renamed Columns2", "Sec Final SEI Value", each if [Sec OverUnder] = "Under" and [Activity Order] <> [MaxActivity] then [Sec Avg Activity SEI] else if [Sec OverUnder] = "Under" and [Activity Order] = [MaxActivity] then [Sec Under SEI New Value] else if [Sec OverUnder] = "Over" and [Sec OverUnder Index] = 1 then [Sec Over SEI New Value] else if [Sec OverUnder] = "Over" and [Sec OverUnder Index] <> 1 then [Sec Over SEI New Value] else 0)
in
    #"Add Sec Final SEI"

[Syndicate_Admin]

Hi @Adam_Payne ,
could be that the reason for this lies in the function for the running total "fxSecRunningTotal". Could you please paste the code for it as well? 

[Syndicate_Admin]

Yeah sure:

let
    Source = (values as list, grouping as list) as list =>

let
    GRTList = List.Generate
    ( 
        ()=> [ GRT = values{0}, counter = 0 ],

        each [counter] < List.Count(values),

        each try 
                 if grouping{[counter]} = grouping{[counter] + 1} 
                 then [GRT = [GRT] + values{[counter] + 1}, counter = [counter] + 1]
                 else [GRT = values{[counter] + 1}, counter = [counter] + 1]
        
             otherwise [counter = [counter] + 1]
    ,
        each [GRT]
    )
in
    GRTList
in
    Source

[Syndicate_Admin]

Hi @Adam_Payne ,
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".

[Syndicate_Admin]

Thanks for that!

OK, i think I understand what this is doing. Do I put this into my main code, or the function for the running total? I think It needs to go into the function fxSecRunningTotal.

[Syndicate_Admin]

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 Total Avg 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([CaseNo], "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"

[Syndicate_Admin]

Hi @Adam_Payne ,
you might have special characters in your column name?
Please find a file attached where the running total function is working with that column name. Hopefully this will help you detect the error in your file.

[Syndicate_Admin]

Thank you so much. I'm still struggling to make it work. 7 hours so far today! Will keep you posted. 

[Syndicate_Admin]

I'm losing my pateince this this!!!! Where am I going wrong?!! Everything I'm trying seems to take ages to run. I also need to replace all the nulls in the Running Total column with zeros. I tried pulling in Replace Values, but that took ages to run.

fxSecRunningTotal

(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"

1 - Data Table

let
    Source = Table.NestedJoin(#"0d - Staging", {"Activity"}, #"0e - AVG", {"Activity"}, "2 - AVG", JoinKind.LeftOuter),
    #"Expanded 2 - AVG" = Table.ExpandTableColumn(Source, "2 - AVG", {"Ops", "MT", "Sec"}, {"Ops", "MT", "Sec"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded 2 - AVG",{{"Ops", "Ops Avg Activity SEI"}, {"MT", "MT Avg Activity SEI"}, {"Sec", "Sec Avg Activity SEI"}}),
    
    //Sort rows by case number and activity order, then remove top two rows as blank
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"CaseNo", Order.Ascending}, {"Activity Order", Order.Ascending}}),
    #"Inserted CaseNoActOrderID" = Table.AddColumn(#"Sorted Rows", "CaseNoActOrderID", each Text.Combine({[CaseNo], Text.From([Activity Order], "en-GB")}, ":"), type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted CaseNoActOrderID",{"AC", "CaseNo", "HighestCaseType", "FCCCreatedDate", "Activity", "Activity Order", "CaseNoActOrderID", "Ops SEI Value", "MT SEI Value", "Sec SEI Value", "Ops Avg Activity SEI", "MT Avg Activity SEI", "Sec Avg Activity SEI"}),
    
    //Add in total of the average SEI values for the case, for that division
    #"Added Ops Tot Avg SEI" = Table.AddColumn(#"Reordered Columns", "Ops Total Avg SEI", each let _item = [CaseNo] in List.Sum(Table.SelectRows(#"Reordered Columns", each [CaseNo] = _item)[#"Ops Avg Activity SEI"])),
    #"Added MT Tot Avg SEI" = Table.AddColumn(#"Added Ops Tot Avg SEI", "MT Total Avg SEI", each let _item = [CaseNo] in List.Sum(Table.SelectRows(#"Reordered Columns", each [CaseNo] = _item)[#"MT Avg Activity SEI"])),
    #"Added Sec Tot Avg SEI" = Table.AddColumn(#"Added MT Tot Avg SEI", "Sec Total Avg SEI", each let _item = [CaseNo] in List.Sum(Table.SelectRows(#"Reordered Columns", each [CaseNo] = _item)[#"Sec Avg Activity SEI"]))
in
    #"Added Sec Tot Avg SEI"

 2 - Max Activity Table

let
    Source = #"1 - Data Table", 
    
    #"Buffer Table" = Table.Buffer(Source),
    #"Grouped Rows" = Table.Group(#"Buffer Table", {"CaseNo"}, {{"MaxActivity", each List.Max([Activity Order]), type nullable number}})
    
in
    #"Grouped Rows"

3c - Sec OverUnder Index

let
    Source = #"1 - Data Table",
    #"Buffered Table" = Table.Buffer(Source),
 
//  Calculate Running Total
    #"Grouped Rows" = Table.Group(#"Buffered Table", {"CaseNo"}, {{"All", each _, type table [Column1=nullable text, Sec Avg Activity SEI=nullable number, Date=nullable date]}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxSecRunningTotal", each fxSecRunningTotal([All], "CaseNo", "Sec Avg Activity SEI")),
    #"Expanded fxSecRunningTotal1" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxSecRunningTotal", {"AC", "CaseNo", "HighestCaseType", "FCCCreatedDate", "Activity", "Activity Order", "CaseNoActOrderID", "Sec SEI Value", "Sec Avg Activity SEI", "Sec Total Avg SEI", "Sec Avg Running Total"}, {"AC", "CaseNo.1", "HighestCaseType", "FCCCreatedDate", "Activity", "Activity Order", "CaseNoActOrderID", "Sec SEI Value", "Sec Avg Activity SEI", "Sec Total Avg SEI", "Sec Avg Running Total"}),

//  Replace Nulls with zeros
    #"Replaced Value" = Table.ReplaceValue(#"Expanded fxSecRunningTotal1",null,0,Replacer.ReplaceValue,{"Sec Avg Running Total"}),

//  Remove 'All' Column as not required
    #"Remove All Column" = Table.RemoveColumns(#"Replaced Value",{"All"}),

//  Change data types to numbers
    #"Changed Type" = Table.TransformColumnTypes(#"Remove All Column",{{"Sec SEI Value", type number}, {"Sec Avg Activity SEI", type number}, {"Sec Total Avg SEI", type number}, {"Sec Avg Running Total", type number}}),

//  Calculate over/under SEI
    #"Add OverUnder" = Table.AddColumn(#"Changed Type", "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"),

//  Group OverUnder by Case
    #"Grouped Rows1" = Table.Group(#"Add OverUnder", {"CaseNo.1", "Sec OverUnder"}, {{"Count", each _, type table [CaseNo=nullable text, AC=text, CaseNo.1=text, HighestCaseType=text, FCCCreatedDate=datetime, Activity=text, Activity Order=number, CaseNoActOrderID=text, Sec SEI Value=nullable number, Sec Avg Activity SEI=nullable number, Sec Total Avg SEI=nullable number, Sec Avg Running Total=nullable number, Sec OverUnder=any]}}),

//  Add index value to OverUnder with Case
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([Count], "Index1", 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"CaseNo", "CaseNoActOrderID", "Index1"}, {"Custom.CaseNo", "Custom.CaseNoActOrderID", "Custom.Index1"}),

//  Remove unrequired columns
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Sec OverUnder", "Count", "Custom.CaseNo"})
in
    #"Removed Columns"

4c - Sec Data Table

let
    Source = #"1 - Data Table",
    #"Buffered Table" = Table.Buffer(Source),

//  Running Total
    #"Grouped Rows" = Table.Group(#"Buffered Table", {"CaseNo"}, {{"All", each _, type table [Column1=nullable text, Sec Avg Activity SEI=nullable number, Date=nullable date]}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxSecRunningTotal", each fxSecRunningTotal([All], "CaseNo", "Sec Avg Activity SEI")),
    #"Expanded fxSecRunningTotal1" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxSecRunningTotal", {"AC", "CaseNo", "HighestCaseType", "FCCCreatedDate", "Activity", "Activity Order", "CaseNoActOrderID", "Sec SEI Value", "Sec Avg Activity SEI", "Sec Total Avg SEI", "Sec Avg Running Total"}, {"AC", "CaseNo.1", "HighestCaseType", "FCCCreatedDate", "Activity", "Activity Order", "CaseNoActOrderID", "Sec SEI Value", "Sec Avg Activity SEI", "Sec Total Avg SEI", "Sec Avg Running Total"}),
    #"Remove All Column" = Table.RemoveColumns(#"Expanded fxSecRunningTotal1",{"All"}),

//  Calculate over/under SEI
    #"Added Over/Under" = Table.AddColumn(#"Remove All Column", "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 Over/Under",{{"Sec Total Avg SEI", type number}, {"Sec Avg Running Total", type number}}),
    
//  Calculate remaining balance
    #"Calculate Remaining Balance" = Table.AddColumn(#"Changed Type", "Sec SEI Balance", each [Sec SEI Value] - [Sec Avg Running Total], type number),
    
//  Calculate Final Activity and display in new column
    #"Max Activity Find" = Table.NestedJoin(#"Calculate Remaining Balance", {"CaseNo"}, #"2 - Max Activity Table", {"CaseNo"}, "Max Activity Table", JoinKind.LeftOuter),
    #"Max Activity Display" = Table.ExpandTableColumn(#"Max Activity Find", "Max Activity Table", {"MaxActivity"}, {"MaxActivity"}),

//  Calculate updated Running Total
    #"Grouped Rows2" = Table.Group(#"Max Activity Display", {"CaseNo"}, {{"All", each _, type table [Column1=nullable text, Sec Avg Activity SEI=nullable number, Date=nullable date]}}),
    #"Invoked Custom Function2" = Table.AddColumn(#"Grouped Rows2", "fxSecRunningTotal", each fxSecRunningTotal([All], "CaseNo", "Sec Avg Activity SEI")),
    #"Expanded fxSecRunningTotal2" = Table.ExpandTableColumn(#"Invoked Custom Function2", "fxSecRunningTotal", {"AC", "CaseNo", "HighestCaseType", "FCCCreatedDate", "Activity", "Activity Order", "CaseNoActOrderID", "Sec SEI Value", "Sec Avg Activity SEI", "Sec Total Avg SEI", "Sec Avg Running Total2"}, {"AC", "CaseNo.1", "HighestCaseType", "FCCCreatedDate", "Activity", "Activity Order", "CaseNoActOrderID", "Sec SEI Value", "Sec Avg Activity SEI", "Sec Total Avg SEI", "Sec Avg Running Total2"}),

//  Remove 'All' column as not required
    #"Remove All Column2" = Table.RemoveColumns(#"Expanded fxSecRunningTotal2",{"All"}),

//  Calculates the additional value to force final number to balance back to total SEI
    #"Calculate additional value for max activity" = Table.AddColumn(#"Remove All Column2", "Sec Over SEI Value", each if [Sec OverUnder] = "Over" then ([Sec Avg Running Total2] + [Sec SEI Balance]) - [Sec Avg Activity SEI] else 0),
    
//  Indexes activities within a case and whether they are over or under the SEI for the case. This is to establish additional apportionment values
    #"Added Index to Case and Over/Under" = Table.NestedJoin(#"Calculate additional value for max activity", {"CaseNoActOrderID"}, #"3c - SecOverUnder Index", {"Custom.CaseNoActOrderID"}, "3c - SecOverUnder Index", JoinKind.LeftOuter),
    #"Expanded 3c - SecOverUnder Index" = Table.ExpandTableColumn(#"Added Index to Case and Over/Under", "3c - SecOverUnder Index", {"Custom.Index1"}, {"Sec OverUnder Index"}),
    
//  SEI value if running total is greater than the total SEI for that case
    #"Handling of SEI when running total is greater than total value" = Table.AddColumn(#"Expanded 3c - SecOverUnder Index", "Custom", each if [Sec OverUnder] = "Over" and [Sec OverUnder Index] = 1 then [Sec Avg Activity SEI] + [Sec SEI Balance] else 0),
    #"Renamed Columns1" = Table.RenameColumns(#"Handling of SEI when running total is greater than total value",{{"Custom", "Sec Over SEI New Value"}}),
    
//  SEI value if running total is under and it's the final activity
    #"Handling of final activity if running total is under total value" = Table.AddColumn(#"Renamed Columns1", "Custom", each if [Sec OverUnder] = "Under" and [Activity Order] = [MaxActivity] then [Sec Avg Activity SEI] + [Sec SEI Balance] else 0),
    #"Renamed Columns2" = Table.RenameColumns(#"Handling of final activity if running total is under total value",{{"Custom", "Sec Under SEI New Value"}}),
    
//  Final Sec SEI Value
    #"Add Sec Final SEI" = Table.AddColumn(#"Renamed Columns2", "Sec Final SEI Value", each if [Sec OverUnder] = "Under" and [Activity Order] <> [MaxActivity] then [Sec Avg Activity SEI] else if [Sec OverUnder] = "Under" and [Activity Order] = [MaxActivity] then [Sec Under SEI New Value] else if [Sec OverUnder] = "Over" and [Sec OverUnder Index] = 1 then [Sec Over SEI New Value] else if [Sec OverUnder] = "Over" and [Sec OverUnder Index] <> 1 then [Sec Over SEI New Value] else 0)
in
    #"Add Sec Final SEI"