Ok, here's steps taken to set it up.
NOTE: PowerQuery is case sensitive, always use correct syntax and case for "M" or PowerQuery language. If I used lower case in function then it should be kept as is.
NOTE2: For this to work, it is important that table is sorted oldest to newest (by date & time). If you may change sort order of original table, I'd recommend adding additional step to sort table before Index column is added.
1. Added Sheet1 M3 & M4 as named range, "vDiam" and "vArea" respectively. To reference it where needed in the Query Editor. (I found later that I only needed vArea)
2. Copied SCADA portion of data to Sheet2, converted it to Excel Table to load to PowerQuery (PQ)
3. Load the table to PQ. Then add 0 based Index column [Index]. To be used later in calculations
4. Open advanced editor in Query Editor (found under "View"). Added line just after "let" to define "mArea" variable to be used in calculation
let
mArea = Excel.CurrentWorkbook(){[Name="vArea"]}[Content]{0}[Column1],
5. Added custom column to return previous row's value for [6_well_level] and named it [PrevWell] using formula below
=if [Index] > 0 then #"Added Index"{[Index]-1}[6_well_level] else 0
6. Added custom column for net flow calculation using "mArea" variable defined in step 4. [Net Flow]
if [Index] > 0 then (([6_well_level]-[PrevWell])*mArea)*60 else 0
7. Since I'm going to reference table itself to do custom calculation later, at this point, created copy of the table and named it "Result"
8. In Query Editor, create new blank query (right click on right pane), copy and paste below custom function in the advanced editor. This is used to obtain last positive flow from previous rows.
(mytable as table, myIndex)=>
let
Source = Table.Max(Table.SelectRows(mytable,each [Index] < myIndex and [Net Flow] >= 0),"Index")[Net Flow]
in
Source
9. In Result table, add custom column [Out Flow] with calculation below. This is the reason copy of Table1 was created (to avoid circular reference to the table)
= if [Net Flow] < 0 then Number.Abs([Net Flow] + GetPositiveFlow(Table1, [Index])) else 0
10. Add [Net Flow In/Out] with formula below
= if [Net Flow] = 0 then "No Change" else if [Net Flow] > 0 then "In" else "Out"
11. Add [Active Pump] column with formula below
= if [6_p1_amps] > 0 and [6_p2_amps] > 0 then "Both" else if [6_p1_amps] > 0 then "Pump 1" else if [6_p2_amps] > 0 then "Pump 2" else "None"
12. Remove all columns before [Net Flow] since it's present in original data or just intermediate steps.
13. Change column data type to appropriate type (Deciman Number or Text)
14. Load back to the sheet
LinkBack URL
About LinkBacks
JeremyWyatt6 
Compact function to use most recent positive value in column.
Register To Reply
Originally Posted by JohnTopley
Bookmarks