SOLVED

# Excel power query calculated column offset cells

Occasional Contributor

# Excel power query calculated column offset cells

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?

7 Replies
best response confirmed by ITTom365 (Occasional Contributor)
Solution

# Re: Excel power query calculated column offset cells

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...

# Re: Excel power query calculated column offset cells

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} ``

# Re: Excel power query calculated column offset cells

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

# Re: Excel power query calculated column offset cells

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?

# Re: Excel power query calculated column offset cells

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

# Re: Excel power query calculated column offset cells

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:

-Duplicated startTime column using the index offset

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

Thanks again

# Re: Excel power query calculated column offset cells

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

Regards