Power Query Problems

How to Count Consecutive Positive and Negative Streaks in Excel

Emma Brown Oct 12, 2026 870 views

Question details

The user needs to identify and count the lengths of consecutive positive and negative number streaks within a large dataset.

How to Count Consecutive Positive and Negative Streaks in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Analyzing a large column of numerical data or percentages to track continuous positive or negative trends and evaluate their duration.
Observed behavior: The goal is to calculate the length of consecutive runs and display the results only at the end of streaks containing at least two values, formatted based on the streak's sign.

Before you start

Ensure your data column does not contain blank cells or text values that might disrupt the grouping logic, and decide how you want to handle zero values before calculating streaks.

Solution 1Recommended

Use Power Query with GroupKind.Local (Recommended)

Power Query is the most robust method for analyzing streaks because the Table.Group function with the GroupKind.Local parameter can accurately group consecutive identical categories.

By classifying numbers as Positive or Negative and applying a local grouping, Power Query evaluates adjacency. This means the grouping breaks and restarts whenever the sign changes, perfectly isolating each streak.

Load data into Power Query

Select your data range, navigate to the 'Data' tab, and click 'From Table/Range' to open the Power Query Editor. Ensure your numbers are formatted correctly using Table.TransformColumnTypes.

Add a classification column

Go to 'Add Column' > 'Custom Column'. Create a formula to classify the data, such as: if [Value] > 0 then "Positive" else if [Value] < 0 then "Negative" else "Zero".

Group rows locally

Use the grouping feature on your new column. Open the Advanced Editor and manually append the GroupKind.Local parameter to your Table.Group step. This ensures only consecutive identical values are grouped together.

Count and expand the groups

Within your grouping step, add an aggregation to count the rows using Table.RowCount. Expand the grouped table (Table.ExpandTableColumn) to align the streak count with your original rows.

Filter and load results

Filter the results to only show counts at the end of streaks that are greater than or equal to 2. Click 'Home' > 'Close & Load' to output the final data into your Excel worksheet.

Use Power Query with GroupKind.Local (Recommended)

Automation Advantage: Once configured, this Power Query will automatically update the streak counts whenever you add new data and hit 'Refresh'.

Solution 2

Use Dynamic Array Formulas (Microsoft 365)

If you are using Microsoft 365, you can utilize dynamic array functions to evaluate adjacent cells and calculate streaks directly in the grid without using Power Query.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Analysis

While advanced M-code modifications in Power Query are native to Microsoft Excel, WPS Office provides a powerful, lightweight, and free alternative for managing complex datasets. It supports an extensive array of advanced formulas and functions, giving you the tools to analyze data trends effortlessly.

1. Download WPS Office: Visit the official WPS website and download the free, lightweight installer for your operating system.
2. Install the Suite: Run the installer and follow the simple on-screen instructions to set up the software in minutes.
3. Open Your Spreadsheets: Launch WPS Spreadsheet and directly open your existing Excel workbooks to continue your data analysis without formatting loss.

100% compatible with Microsoft Excel (.xlsx, .xls, .csv) file formatsEasily apply nested logic formulas and data analysis tools to track numerical streaksLightweight design ensures fast loading and smooth scrolling through massive datasetsFree to download and use with a highly familiar, intuitive user interface

microsoft office alternative - wps office

Frequently Asked Questions

What does GroupKind.Local do in Power Query?

Unlike the standard Group By function which aggregates all identical values across an entire dataset, GroupKind.Local only groups rows that are immediately adjacent to each other. This makes it the perfect tool for identifying consecutive streaks.

How should I handle zero values when tracking positive and negative streaks?

It is best to handle zeroes by assigning them a distinct category (e.g., 'Zero') during your initial classification step. This ensures that a zero will accurately break a positive or negative streak rather than being incorrectly grouped into one.

Can I count consecutive streaks using standard Excel formulas instead of Power Query?

Yes. You can create a helper column using standard formulas. For example, by comparing the current cell to the cell above it (e.g., =IF(SIGN(A2)=SIGN(A1), B1+1, 1)), you can increment a counter. However, this method is less dynamic than Power Query and requires manual dragging.

Excel & Data Problems