Loop using the output from previous row as variable

[Docholliday666]

Hi, I'm posting this question after researching and not finding similar question brought up.

I have report that shows the initial quantity, quantity in and quantity out and the week number, I would like to add a new column New_initial which equals to previous week initial + previous week IN - previous week OUT

I there a way to do that in power query M. Thanks in advance.

Desired outcome

[ronrsnfld · Accepted solution]

There are several  ways to do this. Here's a method using the List.Generate function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABKmQGyuFKsTrWQEZBkghEFCxlAhkCJDI7CQCVTIAqIoFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WEEK = _t, INITIAL = _t, IN = _t, OUT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WEEK", Int64.Type}, 
        {"INITIAL", Int64.Type}, {"IN", Int64.Type}, {"OUT", Int64.Type}}),

    #"Add New Initial" = 
        Table.FromColumns(
            Table.ToColumns(#"Changed Type") &
                {List.Generate(
                    ()=>[a=#"Changed Type"[INITIAL]{0}, idx=0],
                    each [idx] < Table.RowCount(#"Changed Type"),
                    each [a = [a]+#"Changed Type"[IN]{[idx]} - #"Changed Type"[OUT]{[idx]}, idx=[idx]+1],
                    each [a])},
            type table[WEEK=Int64.Type,INITIAL=Int64.Type, IN=Int64.Type, OUT=Int64.Type, NEW_INITIAL=Int64.Type]),
    
    #"Reordered Columns" = Table.ReorderColumns(#"Add New Initial",{"WEEK", "INITIAL", "NEW_INITIAL", "IN", "OUT"})
in
    #"Reordered Columns"

Source

Results

[ronrsnfld · Accepted solution]

There are several  ways to do this. Here's a method using the List.Generate function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABKmQGyuFKsTrWQEZBkghEFCxlAhkCJDI7CQCVTIAqIoFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WEEK = _t, INITIAL = _t, IN = _t, OUT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WEEK", Int64.Type}, 
        {"INITIAL", Int64.Type}, {"IN", Int64.Type}, {"OUT", Int64.Type}}),

    #"Add New Initial" = 
        Table.FromColumns(
            Table.ToColumns(#"Changed Type") &
                {List.Generate(
                    ()=>[a=#"Changed Type"[INITIAL]{0}, idx=0],
                    each [idx] < Table.RowCount(#"Changed Type"),
                    each [a = [a]+#"Changed Type"[IN]{[idx]} - #"Changed Type"[OUT]{[idx]}, idx=[idx]+1],
                    each [a])},
            type table[WEEK=Int64.Type,INITIAL=Int64.Type, IN=Int64.Type, OUT=Int64.Type, NEW_INITIAL=Int64.Type]),
    
    #"Reordered Columns" = Table.ReorderColumns(#"Add New Initial",{"WEEK", "INITIAL", "NEW_INITIAL", "IN", "OUT"})
in
    #"Reordered Columns"

Source

Results

[Docholliday666]

It worked, thank you so much ronrsnfld. Is it possible to do the calculation within every product id as shown below?

I forgot to include the product id variable on my initial request I appologize for that.

Desired outcome

[dufoq3]

Hi @Docholliday666,

Result:

v1 List.Generate

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6xEcAgCAXQXX5tAYhJduHYf42oMXAUQPEOPmZgNDDRN2Ybs254M0gKpS7pVdYKyxat8sTKiZFI+EESIuUc65Wu+Ewr6E5yfwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WEEK = _t, #"PRODUCT ID" = _t, INITIAL = _t, IN = _t, OUT = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"INITIAL", type number}, {"IN", type number}, {"OUT", type number}}),
    GroupedRows = Table.Group(ChangedType, {"PRODUCT ID"}, {{"All", each _, type table}}),
    Ad_NewInitial = Table.AddColumn(GroupedRows, "NEW INITIAL", each 
        [ t = Table.Buffer(Table.SelectColumns([All], {"INITIAL", "IN", "OUT"})),
          lg =
                List.Generate(
                    ()=> [ x = 0,
                           _initial =  t{x}[INITIAL],
                           _newInitial = _initial,
                           _in = t{x}[IN],
                           _out = t{x}[OUT] ],
                    each [x] < Table.RowCount(t),
                    each [ x = [x]+1, 
                           _in = t{x}[IN],
                           _out = t{x}[OUT],
                           _initial = t{x}[INITIAL],
                           _newInitial = [_newInitial] + [_in] - [_out] ],
                    each [_newInitial]
                )
        ][lg], type list),
    Result = Table.FromColumns(Table.ToColumns(Table.Combine(Ad_NewInitial[All])) & { List.Combine(Ad_NewInitial[NEW INITIAL]) }, Table.ColumnNames(GroupedRows{0}[All]) & {"NEW INITIAL"} )
in
    Result

v2 List.Accumulate

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6xEcAgCAXQXX5tAYhJduHYf42oMXAUQPEOPmZgNDDRN2Ybs254M0gKpS7pVdYKyxat8sTKiZFI+EESIuUc65Wu+Ewr6E5yfwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WEEK = _t, #"PRODUCT ID" = _t, INITIAL = _t, IN = _t, OUT = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"INITIAL", type number}, {"IN", type number}, {"OUT", type number}}),
    GroupedRows = Table.Group(ChangedType, {"PRODUCT ID"}, {{"All", each Table.AddIndexColumn(_, "IndexHelper", 0, 1, Int64.Type), type table}}),
    Ad_NewInitial = Table.AddColumn(GroupedRows, "NEW INITIAL", each List.Accumulate( 
    { 0..Table.RowCount([All]) -1 },
    {},
    (s,c)=> s & { if c = 0 then [All]{c}[INITIAL] else s{c-1} + [All]{c-1}[IN] - [All]{c-1}[OUT] }
), type list),
    Result = Table.FromColumns(Table.ToColumns(Table.Combine(Ad_NewInitial[All])) & { List.Combine(Ad_NewInitial[NEW INITIAL]) }, Table.ColumnNames(GroupedRows{0}[All]) & {"NEW INITIAL"} )
in
    Result

[ronrsnfld · Accepted solution]

You would use the same method, just embed it as an aggregation in the Table.Group function:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WEEK", Int64.Type}, {"PRODUCT ID", Int64.Type}, {"INITIAL", Int64.Type}, {"IN", Int64.Type}, {"OUT", Int64.Type}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PRODUCT ID"}, {
        {"New Initial", (t)=>              
          Table.FromColumns(
            Table.ToColumns(t) &
                {List.Generate(
                    ()=>[a=t[INITIAL]{0}, idx=0],
                    each [idx] < Table.RowCount(t),
                    each [a = [a] + t[IN]{[idx]} - t[OUT]{[idx]}, idx=[idx]+1],
                    each [a])},
                    Table.ColumnNames(t) & {"NEW INITIAL"}),
            type table[WEEK=Int64.Type,PRODUCT ID=Int64.Type, INITIAL=Int64.Type, 
                            IN=Int64.Type, OUT=Int64.Type, NEW INITIAL=Int64.Type]}}),

    #"Expanded New Initial" = Table.ExpandTableColumn(#"Grouped Rows", "New Initial", {"WEEK", "INITIAL", "IN", "OUT", "NEW INITIAL"})
in
    #"Expanded New Initial"

[Docholliday666]

It worked!!! Thank you so much I really started to think that power query did not have that capability but you proved me wrong.