Forum Discussion

ITTom365's avatar
ITTom365
Brass Contributor
Oct 11, 2022
Solved

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 comparin...
excel
power bi
  • Lorenzo's avatar
    Lorenzo
    Oct 11, 2022

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

     

ITTom365's avatar
ITTom365
Brass Contributor
Oct 11, 2022

Lorenzo 

 

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}

 

 

Lorenzo's avatar
Lorenzo
Silver Contributor
Oct 11, 2022

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

  • ITTom365's avatar
    ITTom365
    Brass Contributor
    Oct 11, 2022
    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?
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Oct 11, 2022

      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

      • ITTom365's avatar
        ITTom365
        Brass Contributor
        Oct 11, 2022

        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

         

         

         

Resources