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.
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
Power Query - Count of Winning/Losing Streaks
- paspuggie48
- Posts: 665
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
You do not have the required permissions to view the files attached to this post.
- paspuggie48
- Posts: 665
- 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
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