Forum Discussion
jazzyelsie
Dec 20, 2024Copper Contributor
Power Query Formula to Calculate Current Count From Previous Count per Name using Date
Hi everyone!
I have a dataset with the following columns: Point Name, Pump Count, Record Date, and Index. My goal is to create a calculated column in Power Query that computes the following for each Point Name, based on the Record Date:
- If (Current Pump Count - Previous Pump Count) >= 0, apply:
(Current Pump Count - Previous Pump Count) * 0.3
- If the result is negative, use this formula instead:
((1,000,000 - Current Pump Count) + Previous Pump Count) * 0.3
Problem: I tried duplicating the Pump Count column and creating a custom column for the previous count using Table.Buffer, but it didn’t align correctly by Point Name and Record Date. Indices may not be sequential, so referencing the immediate index won’t work. I considered grouping by Point Name and sorting by Record Date to dynamically calculate the Previous Pump Count, but was unsure.
I tried this formula for Previous Pump Count but it doesn’t align correctly for Point Name or handle skipped indices.
My Current Formula:
Table.Buffer(#"Duplicated Column3")[Pump Cycle Count]{[Index] - 1} otherwise null
Desired Process:
- Group by Point Name.
- Sort within each group by Record Date
- Use the formula to compute the difference:
- If (Current Pump Count - Previous Pump Count) >= 0, apply:
(Current Pump Count - Previous Pump Count) * 0.3.
- Else, apply:
((1,000,000 - Current Pump Count) + Previous Pump Count) * 0.3.
How can I implement this in Power Query, ensuring it dynamically references the Previous Pump Count for the same Point Name, even if indices are skipped or Record Dates are close?
- ItsBhattiBrass Contributor
In Power Query, to calculate the current count from the previous count per name using the date, you can:
- Sort the Data: Sort by Name and Date in ascending order.
- Group by Name: Use the "Group By" option on the Name column.
- Add Index: Within each group, add an index column to create a sequence.
- Shift Count: Use a custom column to calculate the difference between the current and previous count by referencing the previous row using the index.
- Expand the Table: Combine the results back into one table.
This method allows you to calculate differences or derive the current count based on the previous one for each name.
- LorenzoSilver Contributor
Hi
Could you attach* a sample representative workbook?
* If not allowed to attach a file your post, share it with OneDrive, Google Drive or the like