Results 1 to 6 of 6

Thread: Running count

  1. #1
    New Member
    Join Date
    Mar 2005
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Running count


    Running count
    Hi All,

    New to power query in Excel. Is there a way I can get a running count based on a reoccuring value in a column, and start a new running count based on new values?

    Cannot find the equivalent of count/countif in PQ.

    Here's an example of what I'm trying to achieve.

    Input
    Name Values
    A.N.Other01 13
    A.N.Other01 23
    A.N.Other01 100
    A.N.Other01 122
    A.N.Other01 156
    A.N.Other01 347
    A.N.Other01 2135
    A.N.Other02 45
    A.N.Other02 124
    A.N.Other02 783
    A.N.Other02 2345
    A.N.Other03 5
    A.N.Other04 35
    A.N.Other05 67
    A.N.Other06 23
    A.N.Other06 23
    A.N.Other06 32
    A.N.Other06 58
    A.N.Other06 67


    Output
    Name Values Col1 Col2
    A.N.Other01 13 1 0
    A.N.Other01 23 1 1
    A.N.Other01 100 1 2
    A.N.Other01 122 1 3
    A.N.Other01 156 1 4
    A.N.Other01 347 1 5
    A.N.Other01 2135 1 6
    A.N.Other02 45 2 0
    A.N.Other02 124 2 1
    A.N.Other02 783 2 2
    A.N.Other02 2345 2 3
    A.N.Other03 5 3 0
    A.N.Other04 35 4 0
    A.N.Other05 67 5 0
    A.N.Other06 23 6 0
    A.N.Other06 23 6 1
    A.N.Other06 32 6 2
    A.N.Other06 58 6 3
    A.N.Other06 67 6 4


    Thanks,
    Jon
    Last edited by jonh; Apr 15th, 2019 at 10:00 AM.

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,403
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: Running count

    something like this?
    (with your example)

    Name Values CntGrp CountOver
    A.N.Other01
    13
    1
    0
    A.N.Other01
    23
    1
    1
    A.N.Other01
    100
    1
    2
    A.N.Other01
    122
    1
    3
    A.N.Other01
    156
    1
    4
    A.N.Other01
    347
    1
    5
    A.N.Other01
    2135
    1
    6
    A.N.Other02
    45
    2
    0
    A.N.Other02
    124
    2
    1
    A.N.Other02
    783
    2
    2
    A.N.Other02
    2345
    2
    3
    A.N.Other03
    5
    3
    0
    A.N.Other04
    35
    4
    0
    A.N.Other05
    67
    5
    0
    A.N.Other06
    23
    6
    0
    A.N.Other06
    23
    6
    1
    A.N.Other06
    32
    6
    2
    A.N.Other06
    58
    6
    3
    A.N.Other06
    67
    6
    4


    function: fnRunningTotCountOver
    Code:
    (MyTable as table) as table =>
    let
        Source = Table.Buffer(MyTable),
        TableType = Value.Type(Table.AddColumn(Source, "RunningTotCountOver", each null, type number)),
        Cumulative = List.Skip(List.Accumulate(Source[Count],{0},(Cumulative,Count) => Cumulative & {List.Last(Cumulative) + Count})),
        AddedRunningTotCountOver = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
    in
        AddedRunningTotCountOver
    table:
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table19"]}[Content],
        Index = Table.AddIndexColumn(Source, "Index", 1, 1),
        Grp = Table.Group(Index, {"Name", "Values", "Index"}, {{"Count", each Table.RowCount(_), type number}}),
        TType = Value.Type(Table.AddColumn(Grp, "RunningTotCountOver", each null, type number)),
        Grp2 = Table.Group(Grp, {"Name"}, {{"AllData", fnRunningTotCountOver, TType}}),
        ExpAllData = Table.ExpandTableColumn(Grp2, "AllData", {"Values", "RunningTotCountOver"}, {"Values", "RunningTotCountOver"}),
        Extract = Table.AddColumn(ExpAllData, "Last Characters", each Text.End([Name], 1), type text),
        Reorder = Table.ReorderColumns(Extract,{"Name", "Values", "Last Characters", "RunningTotCountOver"}),
        Ren = Table.RenameColumns(Reorder,{{"Last Characters", "CntGrp"}}),
        Subtract = Table.TransformColumns(Ren, {{"RunningTotCountOver", each _ - 1, type number}}),
        Rename = Table.RenameColumns(Subtract,{{"RunningTotCountOver", "CountOver"}})
    in
        Rename
    Last edited by sandy666; Apr 15th, 2019 at 11:17 AM.
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    New Member
    Join Date
    Mar 2005
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running count

    Hi Sandy,

    Yes. Thank you. That's perfect for Col2, how would I get Col1 where I want All ANOther1 to have a value of 1, All ANOther2 to have value of 2 etc...

    Would it be possible to share the workbook on which you created the above code and output?

    Many thanks,
    Jon

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,403
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running count

    refresh thread, it was changed
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    New Member
    Join Date
    Mar 2005
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running count

    Hi Sandy,

    Thank you very much.

    Regards,
    Jon

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,403
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running count

    You are welcome

    Have a nice day

    if it works for you don't forget to use Thanks/Like buttons
    Last edited by sandy666; Apr 15th, 2019 at 11:31 AM.
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •