Oct 10 2022 11:02 PM
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?
Oct 11 2022 12:26 AM
SolutionHi @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] * 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...
Oct 11 2022 02:20 AM
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}
Oct 11 2022 02:35 AM
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
Oct 11 2022 02:39 AM
Oct 11 2022 05:07 AM
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
Oct 11 2022 06:39 AM
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
Oct 11 2022 07:24 AM
Hi @ITTom365
Glad you found a workable solution for your scenario & Thanks for documenting it (can help others...)
Regards
Oct 11 2022 12:26 AM
SolutionHi @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] * 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...