Forum Discussion

Stealth02's avatar
Stealth02
Icon for Helper I rankHelper I
4 years ago
Solved

Power Query - Looping calculation

Hi, I am trying to achive the following results (DesiredResults) through Power Query - using the calculation logic that is there.     I have used a List.Sum(Table.SelectRows) - as I am not trying ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    you are absolutely rigth!

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFTSUTI0QCJidUDiRiCeJUQIyoRKGAJ5YFlDMGkElzAC8oDIHCQKFzSGCJqCBI3hoiYQURNUK02R1IJEYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, device = _t, plan = _t, res = _t]),
    q = Table.TransformColumnTypes(Source,{{"date", Int64.Type}, {"device", Int64.Type}, {"plan", Int64.Type}, {"res", Int64.Type}}),
    calcres=List.Accumulate({0..Table.RowCount(q)-1},{}, (s,c)=> s& {List.Max({0,q[plan]{c} - (List.Sum(List.LastN(s,2))??0)})})
    ttc=Table.ToColumns(q),
    nm=Table.ColumnNames(q),
    tfc=Table.FromColumns(ttc&{calcres}, nm&{"calcres"})
in
   tfc

     

     

  • Stealth02's avatar
    Stealth02
    4 years ago

    I resolved my above issue by creating a function, and then invoking the function following a group by step. I have pasted both codes below as reference to the community - but I would still be interested in a solution that hacks the M code - without having to create a function - if possible, to deepen my M knowledge.

     

    Here is the code that works:

     

    The function (named fnListAccumulate) - which is essentially the code provided by @Rocco_sprmnt21 in the accepted solution - with minor changes to convert to a function (red) :

    (GrpTbl as table)=>

    let
    calcres=List.Accumulate({0..Table.RowCount(GrpTbl)-1},{}, (s,c)=> s& {List.Max({0,GrpTbl[plan]{c} - (List.Sum(List.LastN(s,2))??0)})}),
    ttc=Table.ToColumns(GrpTbl),
    nm=Table.ColumnNames(GrpTbl),
    tfc=Table.FromColumns(ttc&{calcres}, nm&{"calcres"})
    in
    tfc

     

     

    Here is the original table invoking the above function after a group the desired fields (Scenario/Product):

     

    let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZI7DsMgDIbvwpwBTJyEW1TqGGVq90q9/1AewTJgo0rEYfhC/gfnacC6YBbzfLk4H9/PO72dZeNaEgV2oEIBbNnemBuw/KXLEwiDAYtrTwwhXkIwIZ6YVWLWVjiq5xDTRZDEIT2if6j/WbHsRPtQY0RsJMFAxbW1sr3E5IRQsQ/MvuIemPvOPPzVP6cm/XNs0j/H6KwugY45bImqSaBjdukCdMw2iWDSP0f0/jml98+pqhoU97w4UNzfTGjvCErMURO6fg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, scenario = _t, product = _t, device = _t, plan = _t, res = _t]),

    q = Table.TransformColumnTypes(Source,{{"date", Int64.Type}, {"device", Int64.Type}, {"plan", Int64.Type}, {"res", Int64.Type}, {"scenario", type text}, {"product", type text}}),
    #"Grouped Rows" = Table.Group(q, {"scenario", "product"}, {{"Allrows", each _, type table [date=nullable number, scenario=nullable text, product=nullable text, device=nullable number, plan=nullable number, res=nullable number]}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "ListAccumulate", each FnListAccumulate([Allrows])),
    #"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"ListAccumulate"}),
    #"Expanded ListAccumulate" = Table.ExpandTableColumn(#"Removed Other Columns", "ListAccumulate", {"date", "scenario", "product", "device", "plan", "res", "calcres"}, {"date", "scenario", "product", "device", "plan", "res", "calcres"})
    in
    #"Expanded ListAccumulate"

     

     

     

Stealth02's avatar
Stealth02
Icon for Helper I rankHelper I
4 years ago

Anonymous

 

As I am bringing this sample model into my actual model - I realized I missed some complexity... I need to do exactly as described above - but by group - i.e. in the example below - I would like to accumulate by group: i.e. Group 1 - scenario 1 and product 1, Group 2 - Scenario 2 Product 2, etc. Restarting the "s" (of the list accumulate function) between each group. 

 

 

 

I am quasi-certain that the best (simple) way to achive this is to use the group function and all rows and pass the "allrows" table as the table in the list.accumulate function... 

 

I used your code above (and updated the source to include the new columns/content) and added the group row function and updated the list.accumulate function (Text in red). However - with that code - I get the following error (which is likely due to row context): Expression.Error: We cannot convert a value of type List to type Table. I need help in updating the code below so it works! Thanks. 

 

  

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdLBDoMgDAbgd+HsoVSq8hZLdjSetvuSvf9hIkhKoc2SiR6+Kf9f9t0h+Ogm93z5c318P+9098CWY0oKoVMxA8iPhfmOXf/014qVYcfO35pMJfOIUCJzNWFkQrtxUt9TjaggbY7KpRWA94cCGfnx7pHIiI95T4sRH1lDpMRHFl9Jjyy9CI9/zZ8rY/6cGfPnrL5LNCDMBrmppgFh1tEBEGYxKrDmz40+f670+XOlHH9BhsdfmNg2RCOzlU8dPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, scenario = _t, product = _t, device = _t, plan = _t, res = _t]),

    q = Table.TransformColumnTypes(Source,{{"date", Int64.Type}, {"device", Int64.Type}, {"plan", Int64.Type}, {"res", Int64.Type}, {"scenario", type text}, {"product", type text}}),

  

 GrpTbl = Table.Group(q, {"scenario", "product"}, {{"Allrows", each _, type table [date=nullable number, scenario=nullable text, product=nullable text, device=nullable number, plan=nullable number, res=nullable number]}}),

    calcres=List.Accumulate({0..Table.RowCount(GrpTbl[Allrows])-1},{}, (s,c)=> s& {List.Max({0,GrpTbl[Allrows][plan]{c} - (List.Sum(List.LastN(s,2))??0)})}),

    ttc=Table.ToColumns(GrpTbl),

    nm=Table.ColumnNames(GrpTbl),

    tfc=Table.FromColumns(ttc&{calcres}, nm&{"calcres"})

in

   tfc

  • Stealth02's avatar
    Stealth02
    Icon for Helper I rankHelper I
    4 years ago

    I resolved my above issue by creating a function, and then invoking the function following a group by step. I have pasted both codes below as reference to the community - but I would still be interested in a solution that hacks the M code - without having to create a function - if possible, to deepen my M knowledge.

     

    Here is the code that works:

     

    The function (named fnListAccumulate) - which is essentially the code provided by @Rocco_sprmnt21 in the accepted solution - with minor changes to convert to a function (red) :

    (GrpTbl as table)=>

    let
    calcres=List.Accumulate({0..Table.RowCount(GrpTbl)-1},{}, (s,c)=> s& {List.Max({0,GrpTbl[plan]{c} - (List.Sum(List.LastN(s,2))??0)})}),
    ttc=Table.ToColumns(GrpTbl),
    nm=Table.ColumnNames(GrpTbl),
    tfc=Table.FromColumns(ttc&{calcres}, nm&{"calcres"})
    in
    tfc

     

     

    Here is the original table invoking the above function after a group the desired fields (Scenario/Product):

     

    let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZI7DsMgDIbvwpwBTJyEW1TqGGVq90q9/1AewTJgo0rEYfhC/gfnacC6YBbzfLk4H9/PO72dZeNaEgV2oEIBbNnemBuw/KXLEwiDAYtrTwwhXkIwIZ6YVWLWVjiq5xDTRZDEIT2if6j/WbHsRPtQY0RsJMFAxbW1sr3E5IRQsQ/MvuIemPvOPPzVP6cm/XNs0j/H6KwugY45bImqSaBjdukCdMw2iWDSP0f0/jml98+pqhoU97w4UNzfTGjvCErMURO6fg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, scenario = _t, product = _t, device = _t, plan = _t, res = _t]),

    q = Table.TransformColumnTypes(Source,{{"date", Int64.Type}, {"device", Int64.Type}, {"plan", Int64.Type}, {"res", Int64.Type}, {"scenario", type text}, {"product", type text}}),
    #"Grouped Rows" = Table.Group(q, {"scenario", "product"}, {{"Allrows", each _, type table [date=nullable number, scenario=nullable text, product=nullable text, device=nullable number, plan=nullable number, res=nullable number]}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "ListAccumulate", each FnListAccumulate([Allrows])),
    #"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"ListAccumulate"}),
    #"Expanded ListAccumulate" = Table.ExpandTableColumn(#"Removed Other Columns", "ListAccumulate", {"date", "scenario", "product", "device", "plan", "res", "calcres"}, {"date", "scenario", "product", "device", "plan", "res", "calcres"})
    in
    #"Expanded ListAccumulate"