Forum Discussion

Stealth02's avatar
Stealth02
Helper 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 to sum the entire column in my formula - only the last 2/3 years - however, I can't seem to achieve the desired results through PQ. I feel that this would likely be achived through looping (i.e. List.Generate or List.Accumulate) however, all examples that I have seen is a cumulative sum as opposed to a sum for the last 2-3 years.  Is there a way to achieve this?

 

Below is my code and sample table - DesiredResults - is the desired results - CurrentPQResults is what I have obtained in PQ with the coding below:

 

let
Source = Excel.CurrentWorkbook(){[Name="Test2"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"Date", Int64.Type}, {"Device", Int64.Type}, {"Plan", Int64.Type}, {"DesiredResults", Int64.Type}, {"Calculation Logic", type text}}),
Add=Table.AddColumn(ChangedType, "CurrentPQResults",

(x)=>

let

xDev=x[Device],
xPlan=x[Plan],
xDate=x[Date],
rDev=List.Sum(Table.SelectRows(ChangedType, each ([Date] <=xDate and [Date] >=xDate-2))[Device]),
TempAdd1=

if xDev<>null then xDev
else
try if xPlan-rDev<0 then 0 else xPlan-rDev otherwise null

in
TempAdd1, Int64.Type)

in
Add

 

 

 

 

 

 

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"

     

     

     

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
    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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", Int64.Type}, {"device", Int64.Type}, {"plan", Int64.Type}, {"res", Int64.Type}}),
    ai = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(ai, "rescalc", each List.Max({[plan]-((try ai[res]{[Index]-1} otherwise 0) + (try ai[res]{[Index]-2} otherwise 0)),0}))
in
    #"Added Custom"
    • Stealth02's avatar
      Stealth02
      Helper I
      4 years ago

      Thanks. 

      If I understand it correctly - you have used the DesiredResults column to generate the Rescalc column.

       

      If correct, I may not have been clear in my original message - but the desired column was created manually - just for the purpose of showing what I was looking to achieve - and should not be used to generate the Rescalc column. 

       

      Essentially from the table above only the Date, Device and Plan would be part of the intial table - and the calculated column is what I am trying to achieve entirely within PQ. I.e. The current row calculation of the calculated column is dependant on the calculation of the two previous row of that same calculated column.

      • Anonymous's avatar
        Anonymous
        Not applicable
        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
    Helper 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
      Helper 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"