Power Query - Count of Winning/Losing Streaks

Post Reply
User avatar
paspuggie48
Posts: 659
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

A long time ago I used an Excel Function that counted the sequence of winners/losers. Inserting this into a VBA Module worked great but I wondered if I could replicate it with PQ.

Having spent an hour with Bing AI telling him/her that certain M Code functions didn't exist, I took a breather and came back to it with a more basic outline of my requirement. Ironically, the solution was much simpler than I thought.

I added some dummy data to the Input Table (blue). The 'Sequence' column in the Output Table (green) shows the count of winning/losing streaks.

PQStreaks.PNG

Simply paste your data into the 'Input' Table (blue). Select the Output Table (green), right-click your mouse and select 'Refresh'.

You may need to adjust data you have as I use BF results, which denotes winners as 1 and losers as 0. e.g. if you have W or L, then replace the text accordingly.

*caveat; not fully tested ;)

Running Streaks.xlsx
You do not have the required permissions to view the files attached to this post.
Top
User avatar
paspuggie48
Posts: 659
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

* had to make a slight change as one cannot remove rows from a Table adjacent to another.

There are now 2 worksheets, Input & Output

A few hundred rows takes a few seconds to refresh but I tested it with 4,500 results and it took 2.5 minutes to refresh :o
Top
Post Reply

Return to “Excel Power Query”