Forum Discussion

ThornTech's avatar
ThornTech
Frequent Visitor
4 years ago
Solved

Running count that resets

Hi,

 

I have a data set where I want to count the number of Loss or wins in a row, but reset each time it switches to the new name like this:

Win

1
Win2
Win3
Loss1
Loss2
Win1
Win2

 

The Trade UID is the main index for this table that is unique.

 

Any guidance on how to do this would be great, I've tried quite a few different things and the closest I can get it to create a running count, but can't get it to reset.

 

A sample of the dataset can be found here: https://1drv.ms/u/s!Ag_sNauqdCgpg95oGmGBlk9NJrf0Uw?e=hICHI1 

 

Thanks

  • CNENFRNL's avatar
    CNENFRNL
    4 years ago

     

    PQ and Excel formula are way much conciser and more straightforward,

    Grouped = Table.RemoveColumns(Table.Group(Source, "PnL Text", {"ar", each Table.AddIndexColumn(_, "Streak_PQ", 1)}, 0), "PnL Text")

     

2 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago

     

    PQ and Excel formula are way much conciser and more straightforward,

    Grouped = Table.RemoveColumns(Table.Group(Source, "PnL Text", {"ar", each Table.AddIndexColumn(_, "Streak_PQ", 1)}, 0), "PnL Text")

     

  • ThornTech's avatar
    ThornTech
    Frequent Visitor
    4 years ago

    This worked a treat, I used the PQ version in the end. Thanks for quick help.