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.
Hi Dave,
Use Table.Buffer to speedup:
#"Added Index" = Table.Buffer(Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1)),
You can also optimize the evaluation step:
#"Added Custom" = Table.AddColumn(#"Added Index", "Original PO Value", each if [Attribute] = "Passive Build" or [Attribute] = "Active Build" then null else if [Attribute] = "Passive Final Account" or [Attribute] = "Active Final Accout" then #"Added Index"{[Index]-3}[PO Money] else null),
Hi Catalin
Thank you so much fro the response - this makes an amazing difference in speed.
Certainly one to remember for the future.
Thank you.
David
