Query taking forever when using Index

[Skemaz]

Hi

As soon as I add this step:

= Table.AddColumn(AbsoluteSpeedStep, "SpeedBefore", each try AbsoluteSpeedStep[AbsoluteSpeed]{[Index]-1} otherwise [AbsoluteSpeed])

as shown in the 1st screen-shot below, when I either "Close and Apply" or subsequently "Refresh" the data, it almost seems as if its doing a "cartesian product" itereation of all Rows of all Files in the Folder from which my JSON files are combined.

This step takes a very long time to complete.

If I remove that one step, all is OK. But I need to create a column containing the "Speed" from the previous row so I can show increases and decreases of speed over time.

In the below screen-shots, the following describes the numbered items.

(1) I've created an Index column.

(2) This is the column containing the speed for the current row.

(3) This is the column causing the trouble. The formula bar in the screen-shot contains the code shown above (based on an example I found).

(4) This is what I see for about 20 minutes. I have 9 files in my combined folder at present so when I start adding more files the situation will become unworkable.

(5) The same happens if I refresh the data.

Any assistance will be greatly appreciated.

Thanks, Mark.

[RobertSlattery · Accepted solution]

I think this blog by @MattAllington may be what you are looking for.

[Skemaz]

Hi Robert

Many thanks for identifying a great solution.

I get the same result as with the other mothod I discovered - but this time with no noticable additional delay.

So, many thanks indeed!

Best regards, Mark.