SOLVED

Excel power query calculated column offset cells

Occasional Contributor

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

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:

 

_Screenshot.png

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

 

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

 

 

@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

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?

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

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

 

 

 

Hi @ITTom365 

 

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

 

Regards