If condition, comparing two cells with different indicies

[KamalFarid]

I want to form a new table from the table i am working on to perform calculation and present it.

The table is as follows

The new table that i have in mind to be as follows:

Number |          Turn On Time                     |                       Turn Off Time                |             Calculated Time

0            | eventCreationTime@ TurnOn     | eventCreationTime@next TurnOff      |     Difference between both

1

2

3

The steps that i had in mind is as follows:

1- Add customized columns resultsing of the eventCreation Time based on the presence of Turn ON/ Turn Off condition

2- Form new Table Based on these two new columns

The part i am currently stuck at is to form the condition, where the eventCreation Time is provided for the Turn Off time only if a Turn On was given before it. This is because i can not find a way to use the index of the columns and compare the cell with the previous one for instance. I was able to do the first Turn on Time column and it could be seen in this snippit from the Query advanced editor and the result in the Tun On Time photo.

 #"FilteredRows"= Table.SelectRows(#"RemoveCols",each([NewColumn.1.stringValue]="Turn On" or [NewColumn.1.stringValue]="Turn Off")),
    #"Sorted Rows1" = Table.Sort(FilteredRows,{{"eventCreationTime", Order.Ascending}}),
    #"RemCol" = Table.RemoveColumns(#"Sorted Rows1", {"eventCreationTime", "NewColumn.1.stringValue"}),
    #"Table1" = Table.AddColumn(#"Sorted Rows1","Turn On Time", each if [NewColumn.1.stringValue]= "Turn On" then [eventCreationTime] else 0)
in
    #"Table1"

I want to know if there is a way to index the column to be able to compare one cell to the previous one and then use the result of a cell in the relevant column.

Excuse me if this is a simple question, my knowledge in PowerBI is very fresh and i used to work more on Matlab programming where array handling was with different way. Thanks in advance!

[v-sihou-msft]

@KamalFarid

In this scenario, you only have event create time, and you have records like:

Turn On

Turn On

Turn Off

Turn Off

How can you determine the "Turn Off" is associated to which "Turn On"?

So the best approach for your requirement is adding event ID in your source table so that you can group your data on event level. Or you change your rule, make one event can't be turned on until the last one is turned off. Otherwise, it's impossible to get the table with event start and end time for each event as you expected.

Regards,

[KamalFarid]

@v-sihou-msft

Thanks for checking the question and answering.

The case is, i want to put the eventCreation time of the "Turn off" only if there is a "Turn On" before it and neglect the up comming "Turn Off"s a new "Turn On" appears and then the loop is reinitialized. 

So what i have in mind is to compare them together as a sort of array :

IF current cell = "TurnOff" and previous cell="Turn On" 

then 

newcolumn row = current row.eventCreationTime

else 

=0

So now only events will be based on Turn On and other Turn offs will be neglected. Is that possible in terms of PQ?!

Thanks