Forum Discussion

New Member

2 years ago

Solved

Help with recursive function and summing multiple rows on one row

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 ...

Need Help

dufoq3

2 years ago

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

dufoq3

Community Champion

2 years ago

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

New Member

2 years ago

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

Community Champion

2 years ago

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

Forum Discussion

Help with recursive function and summing multiple rows on one row

Related content

Recursive function in Power Query

Recursion Strategy

PowerBI Loop/Recursive Function

Recursive Function in Power Query

Recursive Function Forecasting