Excel power query calculated column offset cells

[ITTom365]

Hello, 

I'm learning power query to replace VBA as the method of getting/transforming data for analysis in excel. 

I am stuck with how to construct a query for a column that requires comparing an offset(-1,-1) cell. 

In excel my formula would be 

=IFERROR(IF(myCell-myOffsetCell <0;"00:00:00";myCell-myOffsetCell;"00:00:00")

Or VBA

"=IFERROR(IF([@[myCell]-R[-1]C[-12]<0,""00:00:00"",[@[myCell]]-R[-1]C[-12]),""00:00:00"")"

Can anyone helpme with how to contruct this in Excel power Query? 

[#2 · Accepted solution]

Hi @ITTom365 

Power Query isn't really designed to do this kind of things. In a nutshell you have to add an Index column and then reference the desired column/field* + the Index for the "row" number. In the attached example:

after adding an Index, the Custom column formula:

if [Amount] < 50
then try [Amount] - #"Added Index"[Order]{[Index]-1} otherwise [Amount]
else [Amount] * 10

says something like:

=IF([@Amount] < 50, IFERROR([@Amount] - OFFSET([@Order],-1,),[@Amount]), [@Amount] * 10)

* Referencing Column-n without knowing its name is also possible...

[#3]

@L z. 

Thanks, it was the need for an index that was missing from my attempts. 

Tested with a simplified formula (no error handling) , works great

each [start]- #"BetweenTime"[end]{[Index]-1} 

[#4]

@ITTom365 

Glad this helped & Thanks for providing feedback

In the example I provided error handling was required otherwise the 1st record would raise Error "The index cannot be negative". Hope this makes sense

[#5]

Hi,

Yes it makes sense, putting error handling in place was my next step however performance seems to be an issue:

I dont know if its my machine/network/code order but the query I posted above is processing at a rate of 3.2rows/second. Any thoughts?

[#6]

Hi @ITTom365 

As I mentioned PQ isn't really designed to do this. Though, 3.x rows/sec. seems very slow. Difficult to say more without seeing your complete query + the # rows & columns involved

In the meantime you can try buffering your [end] column as follow:

each [start]- List.Buffer(#"BetweenTime"[end]){[Index]-1}

Also look at ImkeF approach to Reference previous/next row, it's super efficient

[#7]

Hi @L z. 

Thanks again for all your input/help

List.Buffer made a small differnce, but not enough to make it workable. I tried with the ImkeF link, but kept getting errors on creating the merged table. 

In the end I used a solution found here refer-previous-row-next-row-power-query/ 

Essentially:

-Added index

-Duplicated startTime column using the index offset

-Calculated on the two time columns (now on the same row) 

Thanks again

[#8]

Hi @ITTom365 

Glad you found a workable solution for your scenario & Thanks for documenting it (can help others...)

Regards