Help with recursive function and summing multiple rows on one row

[Hoops84]

Hi, I'm wondering if anyone can help advise about two things I'm trying to achieve in PowerBI / PowerQuery. Firstly I would normally do something like this in SQL server, but for the purpose of this I need to see if it can be done natively in PowerBI / PowerQuery and if not then explore SQL/Python.

The basic data model looks something like this;

The ProductCode is just a string, and there's circa 150,000 of them. The cycle of weeks will be between 7 and 14 (not final yet). Just setting this up front as each calculation needs to be indexed by week FinYearWk and ProductCode - so also need to consider even if this can be done if it's practical across that many combinations.

1) I'd like to sum the next three rows of demand on the current row continually (per ProductCode). So for clarity row 1 should be the sum of demand from row 1, 2, 3. Row 2 should be 2, 3, 4 etc.

2) I'd like do this - If BaseStock is null then BaseStock from row above plus current row Commitment minus current row Demand else current row BaseStock plus current row Commitment minus current row Demand

Hope this all makes sense.

Thanks

Tom

[dufoq3]

Hi @Hoops84,

• for future requests provide sample data in usable format (as table or upload it to google drive for example and send here a link) and also expected result based on sample data. Thanks.

Result

You can edit this code and play with it. Maybe you would like to fill down BaseStock. If yes, do it after CombinedAll step.

let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
        let
            a = Table.Column(tbl, col),
            b = if shift = 0 or shift = null then a else if shift > 0
                then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
                else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
            c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
                ( if newColName <> null then {newColName} else
                    if shift = 0 then {col & "_Duplicate"} else
                    if shift > 0 then {col & "_PrevValue"} 
                    else              {col & "_NextValue"} )),
            d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
        in
            d,
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lEyNDAAkiCGUqwOVAyIjMDCZhAxIyDTGK7OAEUhEJkjVEGVmKKIQMwytERXZmSILmJsjG62CboAUEUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProductCode = _t, BaseStock = _t, Commitment = _t, Demand = _t]),
    ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"BaseStock", "Commitment"}),
    ChangedType = Table.TransformColumnTypes(ReplacedValue,{{"BaseStock", type number}, {"Commitment", type number}, {"Demand", type number}}),
    // Added next and previous values into inner [All] table
    GroupedRows = Table.Group(ChangedType, {"ProductCode"}, {{"All", each fnShift(fnShift(fnShift(_, "Demand", -1, "DemandNext1", type number), "Demand", -2, "DemandNext2", type number), "BaseStock", 1, "BaseStockPrev1", type number), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    Ad_DemandThreeRows = Table.AddColumn(CombinedAll, "Demand Three Rows", each List.Sum({[Demand], [DemandNext1], [DemandNext2]}), type number),
    Ad_BaseStockCalculation = Table.AddColumn(Ad_DemandThreeRows, "BaseStock Calculation", each List.Sum({if [BaseStock] = null then [BaseStockPrev1] else [BaseStock], [Commitment], [Demand]}), type number),
    RemovedColumns = Table.RemoveColumns(Ad_BaseStockCalculation,{"DemandNext1", "DemandNext2", "BaseStockPrev1"})
in
    RemovedColumns

[Hoops84]

Hi @dufoq3 ,

Firstly thank you for the reply, and the amazing code. The Demand Three Rows is perfect, thank you. However the stock calcuation is a bit off, but I didn't quite clearly define that as what I wanted. I just reference the row above without making it clear that it should keep carrying things forward. Is it possible to loop in that way in PQ? Also if it is possible would you expect to see any performance issues with this across a lot of data?

I can attach a sample of real data if that helps, just let me know.

I've just shown here in a simple way, so it would be

It needs to keep extending or using the values from above without dropping anything, whilst the calc seems to roughly work at the start by row three it's dropped all of the stock. If it makes it easier, row one will be the only row with base stock each time, and could even have a closing stock if that helps with rolling forward.

Thanks

Tom

[dufoq3 · Accepted solution]

Hi, check this

let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
        let
            a = Table.Column(tbl, col),
            b = if shift = 0 or shift = null then a else if shift > 0
                then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
                else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
            c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
                ( if newColName <> null then {newColName} else
                    if shift = 0 then {col & "_Duplicate"} else
                    if shift > 0 then {col & "_PrevValue"} 
                    else              {col & "_NextValue"} )),
            d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
        in
            d,

    fnCalcStock = 
        (t as table) as table =>
        let
            BufferSelectedColumns = Table.Buffer(Table.SelectColumns(t, {"BaseStock", "Commitment", "Demand"})),
            CalcStock = List.Generate(
                ()=> [ x = 0, y = List.Sum({ BufferSelectedColumns{x}[BaseStock], BufferSelectedColumns{x}[Commitment], -BufferSelectedColumns{x}[Demand] }) ],
                each [x] < Table.RowCount(BufferSelectedColumns),
                each [ x = [x]+1, y = List.Sum({ [y], BufferSelectedColumns{x}[Commitment], -BufferSelectedColumns{x}[Demand] }) ],
                each [y]
            ),
            Merged = Table.FromColumns(Table.ToColumns(t) & {CalcStock}, Value.Type(t & #table(type table[Calc Stock=number], {})))
        in 
            Merged,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lEyNDAAkiCGUqwOVAyIjMDCZhAxIyDTGK7OAEUhEJkjVEGVmKKIQMwytERXZmSILmJsjG62CboAUEUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProductCode = _t, BaseStock = _t, Commitment = _t, Demand = _t]),
    ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"BaseStock", "Commitment"}),
    ChangedType = Table.TransformColumnTypes(ReplacedValue,{{"BaseStock", type number}, {"Commitment", type number}, {"Demand", type number}}),
    // Added [Demand] next values columns and new [Calc Stock] column into inner [All] table
    GroupedRows = Table.Group(ChangedType, {"ProductCode"}, {{"All", each 
        [ a = fnShift(fnShift(_, "Demand", -1, "DemandNext1", type number), "Demand", -2, "DemandNext2", type number), //Shifted rows for Demand Three Rows Calculation
          b = fnCalcStock(a) //Added Calc Stock Column
        ][b], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    Ad_DemandThreeRows = Table.AddColumn(CombinedAll, "Demand Three Rows", each List.Sum({[Demand], [DemandNext1], [DemandNext2]}), type number),
    RemovedColumns = Table.RemoveColumns(Ad_DemandThreeRows,{"DemandNext1", "DemandNext2"})
    
in
    RemovedColumns