My input data is running sum

karlden

New Member
Joined
May 30, 2016
Messages
3
Hi

I have a file with data that I want to import to excel via power query.

All data in the file are continuous measurements of three water meters, which means that the consumption per hour is always current readings minus the previous one.
To complicate things further, you can also readings to be irregular and not done every hour.

The way I have done it under the sheet New data isnt good, but I did it just to get an example.


Is there any way I can fix this either in the power query import stage or via a formula in the power pivot?
I can not find a formula to get the consumption per hour of data that is stored as a running total.


What I want to do with this is for example some graphs as you can see in the file under the sheet Graph. So that you can monitor the water consumption at different times and occasions over time.


Thank you for your help / Johan
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

MarcelBeug

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
You can add 2 Index columns, starting with 0 and 1 respectively, then merg the table with itself and perform the calculations.

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type number}, {"Area 1", Int64.Type}, {"Area 2", Int64.Type}, {"Area 3", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Area 1", "Area 2", "Area 3"}, {"Previous.Area 1", "Previous.Area 2", "Previous.Area 3"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Previous", each [Previous.Area 1] <> null),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Area 1", "Cumulative Area 1"}, {"Area 2", "Cumulative Area 2"},{"Area 3", "Cumulative Area 3"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Aewa 1", each [Cumulative Area 1] - [Previous.Area 1], Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Area 2", each [Cumulative Area 2] - [Previous.Area 2], Int64.Type),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Area 3", each [Cumulative Area 3] - [Previous.Area 3], Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Cumulative Area 1", "Cumulative Area 2", "Cumulative Area 3", "Index", "Index.1", "Previous.Area 1", "Previous.Area 2", "Previous.Area 3"})
in
    #"Removed Columns"
 

Forum statistics

Threads
1,089,394
Messages
5,407,990
Members
403,176
Latest member
mehtavish1

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top