How to Calculate Difference from Previous Count by Group in Power Query
Question details
Calculate a value in Power Query by comparing the current pump count with the previous count for the same point name, ordered by date, using a specific conditional formula.

- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Analyzing sequential meter or pump readings grouped by specific equipment names over time.
- Observed behavior
- The user needs to reference the previous row's value within specific groups where the global index is non-sequential, and apply a conditional mathematical formula based on the difference.
Ensure your dataset contains columns for the grouping category (e.g., Point Name), a chronological identifier (e.g., Record Date), and the numerical value to be calculated (e.g., Pump Count).
Calculate Difference Using Group By and Custom Index
By grouping the data by Point Name and adding a custom index to each group, you can accurately reference the previous row's pump count regardless of the global row order.
This approach ensures that calculations are isolated to specific items (Point Names) and respects the chronological order of the records.
Click the filter drop-down on the 'Point Name' column and select 'Sort Ascending'. Repeat this action for the 'Record Date' column to ensure chronological order.
Navigate to the 'Transform' tab on the ribbon and click 'Group By'. Select 'Point Name' as the grouping column and choose 'All Rows' for the operation to retain your data.
Open the Advanced Editor from the 'Home' tab. Modify the grouping step code to apply `Table.AddIndexColumn` to each grouped sub-table, creating a sequential index starting from 0.
Add a Custom Column from the 'Add Column' tab. Write a formula to fetch the pump count from the previous row by referencing the current row's index minus 1.
Create another Custom Column for your final logic. Use an `if...then...else` statement: if the difference is >= 0, multiply by 0.3; else calculate `((1000000 - Current Count) + Previous Count) * 0.3`.
Click the expand icon (two diverging arrows) at the top of your grouped column to restore the original rows with the newly calculated values.

Need a Lightweight Alternative for Data Analysis?
If you are dealing with complex data processing and looking for a fast, free, and highly compatible spreadsheet tool, WPS Office is an excellent choice. It offers robust data analysis features, familiar formulas, and seamless compatibility with Microsoft Excel files.
- 1. Download WPS Office: Visit the official WPS website and download the free office suite.
- 2. Open Your Dataset: Launch WPS Spreadsheet and open your .xlsx or .csv data files seamlessly.
- 3. Analyze Data: Utilize WPS Spreadsheet's advanced formulas, PivotTables, and data sorting features to process your records.

Frequently Asked Questions
How do I reference a previous row in Power Query without grouping?
If your data is a single continuous series, you can add an Index column starting from 0 and another starting from 1. Then, merge the query with itself using these indices from the 'Home' tab to align the current row with the previous row.
Why is sorting necessary before grouping in Power Query?
Sorting ensures that the chronological order, such as Record Date, is maintained. When the index is applied within each group, it guarantees that the 'previous' row is the chronologically preceding record.
How do I handle null values for the first row in a group?
When referencing the previous row for the first record in a group, the result is null. You can wrap your custom column calculation in a `try...otherwise` statement to output a default value, such as 0, avoiding calculation errors.




