Yes, this can be automated. There are three supported approaches, depending on complexity and maintainability.
Recommended Option (Simple and Reliable)
Keep calculations inside Excel, let Power Automate just add rows
Put a formula in the Excel calculation column that subtracts the previous day’s value.
Excel automatically applies the formula to new rows added by Power Automate.
How to do it?
Store Excel in SharePoint or OneDrive.
Use a structured Excel Table.
Add a formula column such as:
Excel Cell=[@TodayReading] - XLOOKUP([@MeterID]&[@Date]-1,Table[MeterID]&Table[Date],Table[Reading])
Power Automate uses Add a row into a table only.
Excel does the math automatically for each new submission.
Best practice for recurring calculations
Easy to maintain
No Flow complexity
Alternate Option 1 – Calculate inside Power Automate
Power Automate fetches yesterday’s value and subtracts it before writing to Excel.
How to do it?
Use List rows present in a table (filter yesterday + same meter).
Use expression:
sub(float(today), float(yesterday))
Save result as a static value in Excel.
Works without Excel formulas
More complex flow logic
Alternate Option 2 – Office Scripts (Advanced)
Use Office Scripts to run Excel logic like a macro from Power Automate.
How to do it?
Create a script in Excel that calculates differences.
Call it using Run script action.
Best for complex Excel models
Requires scripting knowledge
✅ If this answer helped resolve your issue, please mark it as Accepted so it can help others with the same problem.
👍 Feel free to Like the post if you found it useful.
