Forum Discussion
Excel power query calculated column offset cells
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...
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}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?
