none
Cumulative sum based on condition using MOD RRS feed

  • Question

  • Hi : My final output is in column-I, which I developed in excel. Would it be possible to achieve the same outcome in PQ. May be it may need fewer steps in PQ

    I used a number of helper columns to get Col -I. 

    Helper -1: Count 1 for everything except OFF (count zero).

    Helper -2: Cumulative Sum based on the pervious column and using mod function to every 21st day.

    Final Working Days: Perform if statement. Please see below the link for sample data.

    https://1drv.ms/x/s!Amc8fiGpDxekhgb7sRIIPlWlWGPB

    Thanks.

    Sunday, July 28, 2019 10:04 AM

Answers

  • Hi

    This code lacks an aggregation, you can't use Number.Mod function on a list. However, adding List.Sum returns an error as somehow a list of numbers turns into a list of text. It's probably a bug

    Number.Mod(List.FirstN(Source[#"Helper-1"], [Index]), 21)

    The fastest fix would be to try Andrey's solution, I tested it and it gives me proper results without any need for edit:

    My personal take is as follows. I made mod so that the days count up to 21 inclusive and without 0, instead of going 19 20 0 1. :

    let
        Source = ______________________________
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Helper-1", Int64.Type}}),
    
    
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
        #"Sorted Rows" = Table.Sort(#"Added Index",{{"Helper-1", Order.Descending}, {"Index", Order.Ascending}}),
        #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Helper-2", 0, 1),
        #"Sorted Rows1" = Table.Sort(#"Added Index1",{{"Index", Order.Ascending}}),
        #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Index"}),
        #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",each [#"Helper-1"],null,
    (v,o,n) => if o = 0 then null else Number.Mod(v,21)+1
    ,{"Helper-2"})
    in
        #"Replaced Value"




    • Edited by S.Risemann Tuesday, August 6, 2019 2:20 PM
    • Marked as answer by M.Awal Wednesday, August 7, 2019 10:39 AM
    Tuesday, August 6, 2019 2:09 PM
  • That's a good question. 

    In a custom replacer function such as (v,o,n), old value ("o") and new value ("n") are interchangeable. So while Aleksei prefers this way:

    Table.ReplaceValue( table, 0, XX, (v,o,n) => if n = "TRUE" then true else false, {"column"})

    it can also be written as 

    Table.ReplaceValue( table, XX, 0, (v,o,n) => if o = "TRUE" then true else false, {"column"})

    When writing a custom Replacer function, apart from the first argument ("v") the two other could switch places and as Aleksei said, usually only one argument is needed. This is because we could write:

    Table.ReplaceValue( table, 0, "zero", Replacer.ReplaceValue, {"column"})
    or 
    Table.ReplaceValue( table, 0, null, (v,o,n) => if v = o then "zero" else v, {"column"})
    or even
    Table.ReplaceValue( table, null, null, (v,o,n) => if v = 0 then "zero" else v, {"column"})

    "o" and "n" usually come into play when you need "each [AnotherColumn]"

    You are correct, "each [#"Helper-1"]" is old value 

    and null = new value and it could be anything since it's not used


    • Edited by S.Risemann Friday, August 9, 2019 8:11 AM
    • Marked as answer by M.Awal Friday, August 9, 2019 10:56 PM
    Friday, August 9, 2019 8:10 AM
  • Does the following work?

    replace = Table.ReplaceValue(ChangedType,
    
    each [Product Name], each if Text.Contains([Comments], "Overtime", Comparer.OrdinalIgnoreCase) 
                                                         or Text.Contains([Comments], "Court OT", Comparer.OrdinalIgnoreCase)
                                                          
                                                          then "Overtime" else [Product Name],
                                                       
                                                           (a,b,c)=> c, {"Product Name"})


    • Marked as answer by M.Awal Wednesday, August 21, 2019 10:04 AM
    Tuesday, August 20, 2019 2:13 PM

All replies

  • Add the following two steps to your M script:

    addedIndex = Table.AddIndexColumn(<PreviousStepName>, "Index"),
    addedCustom = Table.AddColumn(addedIndex, "Helper-2", each if [#"Helper-1"] = 1 then Number.Mod(List.FirstN(Source[#"Helper-1"], [Index]), 21) else null)

    Monday, July 29, 2019 5:26 PM
  • Hello,

    Variant

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        addedIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
        helper1OneOnly = Table.SelectRows(addedIndex, each [#"Helper-1"] = 1)[[Index]],
        addIndexToOneOnly = Table.AddIndexColumn(helper1OneOnly, "Helper-3", 0),
        modIndexToOneOnly = Table.TransformColumns(addIndexToOneOnly, {"Helper-3", each Number.Mod(_, 21) + 1}),
        renameModOnOnly = Table.RenameColumns(modIndexToOneOnly, {"Index", "ModIndex"}),
        addHelper3 = Table.Join(addedIndex, {"Index"}, renameModOnOnly, {"ModIndex"}, JoinKind.LeftOuter)
    in
        addHelper3
    Regards,

    Tuesday, July 30, 2019 11:44 PM
  • Hi: Thanks for looking into it. I am trying replicate the following column in PQ. 

    Wednesday, July 31, 2019 9:56 AM
  • M, what did you think of the posted answers?


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, August 5, 2019 8:49 PM
    Owner
  • Hi Ed: I am not sure if I am doing anything wrong. I tried both the solutions and they are not giving me desired output. 

    Tuesday, August 6, 2019 9:47 AM
  • If you click on any error cell, what error message do you get?
    Tuesday, August 6, 2019 9:58 AM
  • Hi

    Expression.Error: We cannot convert a value of type List to type Number.

    Details:
        Value=List
        Type=Type

    Tuesday, August 6, 2019 10:02 AM
  • Hi

    This code lacks an aggregation, you can't use Number.Mod function on a list. However, adding List.Sum returns an error as somehow a list of numbers turns into a list of text. It's probably a bug

    Number.Mod(List.FirstN(Source[#"Helper-1"], [Index]), 21)

    The fastest fix would be to try Andrey's solution, I tested it and it gives me proper results without any need for edit:

    My personal take is as follows. I made mod so that the days count up to 21 inclusive and without 0, instead of going 19 20 0 1. :

    let
        Source = ______________________________
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Helper-1", Int64.Type}}),
    
    
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
        #"Sorted Rows" = Table.Sort(#"Added Index",{{"Helper-1", Order.Descending}, {"Index", Order.Ascending}}),
        #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Helper-2", 0, 1),
        #"Sorted Rows1" = Table.Sort(#"Added Index1",{{"Index", Order.Ascending}}),
        #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Index"}),
        #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",each [#"Helper-1"],null,
    (v,o,n) => if o = 0 then null else Number.Mod(v,21)+1
    ,{"Helper-2"})
    in
        #"Replaced Value"




    • Edited by S.Risemann Tuesday, August 6, 2019 2:20 PM
    • Marked as answer by M.Awal Wednesday, August 7, 2019 10:39 AM
    Tuesday, August 6, 2019 2:09 PM
  • Hi : Thanks for providing the solution. As per Helper-3 Column, something is aggregating after 4 working days. Also the cycle is not starting again after 21st day.

    https://1drv.ms/x/s!Amc8fiGpDxekhgivBhufTe1SCmIg

    Wednesday, August 7, 2019 10:26 AM
  • Sorry: I forgot to change column reference as per below. It is working now.

    = Table.ReplaceValue(#"Removed Columns",each [#"Helper-1"],null,
    (v,o,n) => if o = 0 then null else Number.Mod(v,21)+1
    ,{"Helper-3"})

    Is n refers to  each [#Helper-1]?

    and the v refers to "if o = 0 then null else Number.Mod(v,21)+1"?

    Thank you very much.

    Wednesday, August 7, 2019 10:39 AM
  • Sorry for the confusing variable names. In your code,

    "v" refers to Helper-3.

    "o" refers to each [#"Helper-1"].

    "n" refers to null, it's not used.


    Aleksei Zhigulin wrote the following post for replacer functions:


    (...) Then I've found out, what abc mean in this formula:

    In most scenarios b is not needed and may be equals any value (for brevity, I usually use 0).

    Lately I use only this syntax of Table.ReplaceValue, on my opinion it is more handy and neat, than default syntax.


    • Edited by S.Risemann Wednesday, August 7, 2019 12:00 PM
    Wednesday, August 7, 2019 11:59 AM
  • Thanks again. I have seen and read that post. It makes sense. The only part I need clarification is :

    parameter "b" in Aleksei example where it says "in many cases its not needed", and "b" is placed in the middle with a & c and refers to the first element after the previous step

    In your example, its slightly different, after the previous step, there is column reference instead of 0 or null and your null is in the middle. 

    Also is "each [#"Helper-1"]" = old value

    and null = new value?

    = Table.ReplaceValue(#"Removed Columns",each [#"Helper-1"],null,

    (v,o,n) => if o = 0 then null else Number.Mod(v,21)+1
    ,{"Helper-3"})

    Wednesday, August 7, 2019 8:42 PM
  • That's a good question. 

    In a custom replacer function such as (v,o,n), old value ("o") and new value ("n") are interchangeable. So while Aleksei prefers this way:

    Table.ReplaceValue( table, 0, XX, (v,o,n) => if n = "TRUE" then true else false, {"column"})

    it can also be written as 

    Table.ReplaceValue( table, XX, 0, (v,o,n) => if o = "TRUE" then true else false, {"column"})

    When writing a custom Replacer function, apart from the first argument ("v") the two other could switch places and as Aleksei said, usually only one argument is needed. This is because we could write:

    Table.ReplaceValue( table, 0, "zero", Replacer.ReplaceValue, {"column"})
    or 
    Table.ReplaceValue( table, 0, null, (v,o,n) => if v = o then "zero" else v, {"column"})
    or even
    Table.ReplaceValue( table, null, null, (v,o,n) => if v = 0 then "zero" else v, {"column"})

    "o" and "n" usually come into play when you need "each [AnotherColumn]"

    You are correct, "each [#"Helper-1"]" is old value 

    and null = new value and it could be anything since it's not used


    • Edited by S.Risemann Friday, August 9, 2019 8:11 AM
    • Marked as answer by M.Awal Friday, August 9, 2019 10:56 PM
    Friday, August 9, 2019 8:10 AM
  • Thank you. Clear as mud. By applying Table.ReplaceValue in this way, there are lot ways codes could be optimised.
    Friday, August 9, 2019 10:56 PM
  • After our previous discussion, I have attempted using Table.ReplaceValue and it producing the desired output. I found it bit tricky when I hit the section which replaces the Replace.ReplacerValue. I had to rewrite the if statement again but Text.Contains is not required as it mentioned before.

    https://1drv.ms/x/s!Amc8fiGpDxekhguCh1n6cQIyWp5V

    Friday, August 16, 2019 9:26 PM
  • Good going, that's a good application of the replacer trick. 

    Since Text.Contains returns a boolean value, it can be directly referenced in the if-statement without comparison

    Table.ReplaceValue(ChangedType,
    0,
    each Text.Contains([Comments], "Overtime", Comparer.OrdinalIgnoreCase),
    (a,b,c) => if c then "Overtime" else a, {"Product Name"})


    Monday, August 19, 2019 6:28 AM
  • Hi : Thanks for your reply. I used Replacer.ReplaceValue instead of the approach below and it worked. However, I have to admit I find the approach below is bit tricky. I got stuck.

    replace = Table.ReplaceValue(ChangedType,

    each [Product Name], each if Text.Contains([Comments], "Overtime", Comparer.OrdinalIgnoreCase)
                                                         or Text.Contains([Comments], "Court OT", Comparer.OrdinalIgnoreCase)
                                                         
                                                          then "Overtime" else [Product Name],
                                                      
                                                           (a,b,c)=> if c then c else a, {"Product Name"})


    Monday, August 19, 2019 9:44 AM
  • replace = Table.ReplaceValue(ChangedType,
    
    each [Product Name], each if Text.Contains([Comments], "Overtime", Comparer.OrdinalIgnoreCase) 
                                                         or Text.Contains([Comments], "Court OT", Comparer.OrdinalIgnoreCase)
                                                          
                                                          then "Overtime" else [Product Name],
                                                       
                                                           (a,b,c)=> if c then c else a, {"Product Name"})

    What are you trying to do? To me it looks like it should work

    Do you get error messages?

    • Edited by S.Risemann Monday, August 19, 2019 12:24 PM
    Monday, August 19, 2019 12:24 PM
  • Hi : Sorry for lack of details. I don't get any error message. However, I don't get my desired output. Based Text search "Overtime" or "Court OT" in Column A [Comments] I want to change the column b [Product Name], which would be "Overtime", using this approach (a,b,c)=>. I tried with Replacer.ReplaceValue and I can make it work.

    Tuesday, August 20, 2019 10:51 AM
  • Does the following work?

    replace = Table.ReplaceValue(ChangedType,
    
    each [Product Name], each if Text.Contains([Comments], "Overtime", Comparer.OrdinalIgnoreCase) 
                                                         or Text.Contains([Comments], "Court OT", Comparer.OrdinalIgnoreCase)
                                                          
                                                          then "Overtime" else [Product Name],
                                                       
                                                           (a,b,c)=> c, {"Product Name"})


    • Marked as answer by M.Awal Wednesday, August 21, 2019 10:04 AM
    Tuesday, August 20, 2019 2:13 PM
  • Thanks, it works. I thought I would need a if statement where I marked below. In what scenarios we need if statement twice: where the new value is and as well as here (a,b,c)= if c = 0....
    Wednesday, August 21, 2019 10:04 AM
  • You'll only need several if-statements if the normal "then, else" scenarios aren't enough. Then you can of course write them as "if then else if then else" or 

    y = if x=1 then 1 else x
    
    if y = 2 then 2 else 3
    
    x==[1,2 or 3]

    You only had the "if comment indicates it's overtime, then overtime else product name" so since all that logic fit into your "c" variable then you don't need additional logic in (a,b,c)=>

    Wednesday, August 21, 2019 11:03 AM
  • Thanks. Make sense.
    Wednesday, August 21, 2019 11:19 AM