Power Query Recursive Custom Column

[UPCBishop]

Hello everyone,

I am attempting to create a recursive column in Power Query for a master schedule. Normally this schedule is done manually and I am trying to remove the manual aspect and make it more automated using Power Apps, SharePoint list and Power BI. 

Where I have ran into trouble is getting the last bit of logic I need (and the largest chunk of it) to actually work. 

What I am trying to do is use Power Query to create a custom column that will call on the row directly above it that I will call "state" and multiply it by a value in another column on the row that I would call "current".

Below is an image of what I am attempting to create (obviously I used excel to try out my workflow). The column highlighted in yellow is what I am attempting to create, the other columns currently exist.

From my if statement in excel, you can see that I am first checking to see if the cell in column D has a 1 in it which denotes that this is the start of the project and If the cell does have a 1, then it is multiplied by column C and column F to return the "state", if not the formula continues and looks for the "state" cell (directly above it, if D does not have a 1) and then multiplies the "state" cell by the "current" cell in column F.

I have tried many things througout today and have come up empty handed. I am sure there is something out there in the vast world of Power Query and M language that I am missing, and maybe I just need my braid jogged a bit to remember something. 

[Jakinta · Accepted solution]

Maybe this can help. Paste it in blank query and adjust to your query.

I have not rounded N_Actual column. It can be done easily with Number.Round or you can change column data type to integer (Whole Number) - according to your need.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipNSU8tUdJRMjIEEhAcqxOt5JJanJmeBxM3AGE9S7CMU2lmTgo2iYCi/JJ8VAkLsIRrWWIONnG43YYW2O0Gi2OzG0MCZjeSBIrdqOKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Activity = _t, N_Start = _t, IsStart = _t, Factor = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Activity", type text}, {"N_Start", Int64.Type}, {"IsStart", Int64.Type}, {"Factor", type number}}),
    Ind = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(Ind, "Filter", each if [IsStart] = 1 then [Index] else null),
    Filled = Table.FillDown(#"Added Custom1",{"Filter"}),
    ActualCol = Table.AddColumn(Filled, "N_Actual", each if [IsStart]=1 then [N_Start]*[Factor] else [N_Start]*List.Accumulate(List.Range(Filled[Factor],[Filter],[Index]-[Filter]+1),1,(s,c)=>s*c )),
    #"Removed Columns" = Table.RemoveColumns(ActualCol,{"Index", "Filter"}),
    FINAL = Table.ReorderColumns(#"Removed Columns",{"Activity", "N_Start", "IsStart", "N_Actual", "Factor"})
in
    FINAL

[UPCBishop]

That worked perfectly! I have messed around with List.Accumulate(List.Range...pretty much all day...seems I was missing a few steps before I could really get to that point though.

Thank you very much! This worked like a charm and it's quick. It updated about 5000 records in about 30 seconds. 

[edhans]

Power Query cannot reference a previous or subsequent row like Excel does. Most solutions you see will be painfully slow on larger data sets, even as small as 10,000 records.

The method @ImkeF has here isn't too difficult to get your head around, and it will work on most data sets Power Query can handle and do it with speed. Fast and easy way to reference previous or next rows in Power Query or Power BI – The BIccountant

What it does is turn the column you want to reference in to a list, then adds/removes an item to the top so it shifts the desired direction (up/down) then re-assembles the column into the table, then you just use simple logic to reference your original column and the adjusted column.

I've used this technique many times.