Forum Discussion
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?
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] * 10says something like:
=IF([@Amount] < 50, IFERROR([@Amount] - OFFSET([@Order],-1,),[@Amount]), [@Amount] * 10)* Referencing Column-n without knowing its name is also possible...
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] * 10says something like:
=IF([@Amount] < 50, IFERROR([@Amount] - OFFSET([@Order],-1,),[@Amount]), [@Amount] * 10)* Referencing Column-n without knowing its name is also possible...
