Index 'offset' really slowing refresh times|Power Query|Excel Forum|My Online Training Hub

Hi

I need to create a column which takes the value from another column two rows above, based on a value in a 3rd columns

My code snippet looks like this.

#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Original PO Value", each if [Attribute] = "Passive Build" then null else if [Attribute] = "Active Build" then null else if

[Attribute] = "Passive Final Account" then #"Added Index"{[Index]-3}[PO Money] else if

[Attribute] = "Active Final Accout" then #"Added Index"{[Index]-3}[PO Money] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "PO App ", each [Agreed Value] -[Original PO Value])
in
#"Added Custom1"

I have added an Index olumn starting at 1, which seems to mean that the offset is -3 even though the values are coming from only 2 rows above..

Any way - for approx 1600 rows of data this is slowing what was a fast query down to 3 mins.

Any clues as to why, and any pointers on how to get round this would be gratefully received.

GreenBoy

PS - i have already turned off the Date > Connection> Enable background refresh for this query and any that it refers to.