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...
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
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 https://community.powerbi.com/t5/user/viewprofilepage/user-id/1522 approach to https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/, it's super efficient
Hi Lorenzo
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 https://goodly.co.in/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