Need to calculate time Elapsed in Power Query (B2-B1) then (B3-B2)..........

[srehman]

Time Elapsed.xlsx
	A	B	C
1	Count 1	Count Time	Time Elapsed
2	1	5:20:38	#VALUE!
3	2	5:20:43	0:00:05
4	3	5:20:45	0:00:02
5	4	5:20:47	0:00:02
6	5	5:20:49	0:00:02
7	6	5:20:51	0:00:02
8	7	5:21:19	0:00:28
9	8	5:21:24	0:00:05
10	9	5:21:26	0:00:02
11	10	5:21:28	0:00:02
STOCK_CHECK_DETAILS_by_date_ran

Cell Formulas
Range	Formula
C2:C11	C2	=B2-B1

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=STOCK_CHECK_DETAILS_by_date_ran!$B$1:$C$1	C2

 

[sandy666]

maybe IFERROR(B2-B1,0)

 

[srehman]

Yes i used as well in Excel, i want to use this Logic in power query how can i define b2-b1, b3-b2 ,

 

[sandy666]

try adapt code from MarcelBeug

How to do a running Sum by group in Power Query?

Hi Everyone, I am trying to do a running sum by group in Power Query (m language). Thank you. All solutions I found was to use DAX which I cannot use for my data at this time. Here is what my data looks like, I would like a running sum of the cost in a new column. Thank you all.

community.powerbi.com

 

[alansidman]

Add two index columns to your table. The first starting with 1, the second with zero. Merge the table with itself and join the first index with the second index. Then use this Mcode on the merged table

let
    Source = Table.NestedJoin(Table1, {"Index"}, Table1, {"Index.1"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Count Time"}, {"Table1.Count Time"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table1", "Difference", each [Table1.Count Time]-[Count Time]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Table1.Count Time"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each Duration.Seconds([Difference])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom", "Seconds diff"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Difference"})
in
    #"Removed Columns1"

Book9
	A	B	C
1	Count 1	Count Time	Seconds diff
2	1	5:20:38 AM	5
3	2	5:20:43 AM	2
4	3	5:20:45 AM	2
5	4	5:20:47 AM	2
6	5	5:20:49 AM	2
7	6	5:20:51 AM	28
8	7	5:21:19 AM	5
9	8	5:21:24 AM	2
10	9	5:21:26 AM	2
11	10	5:21:28 AM
Sheet2

 

[srehman]

Perfect Working. Brilliant Logic

Thanks Alansidman

 

[alansidman]

You are welcome.