Calculate Lag and Lead value using power query.

Lag and Lead are functions used in many programming languages that provides access to a row at a specified physical offset which comes before (Lag) or after (Lead) the current row. These functions can be very useful for calculating the difference between the current row and the previous row or the next row.

Method 1

Data source:

Load data into Power Query and add an Index starting at 1 incrementing by 1.

Step 1: Calculating Lag (1) using List.Range function and an Index.

Syntax:

List.Range(list as list, offset as number, optional count as nullable number) as list 

About:

Returns a subset of the list beginning at the offset list. An optional parameter, offset, sets the maximum number of items in the subset.

Lag(1)=List.Range(Source[Value], [Index]-2,1)

The first row returned an error, to get rid of it add to the code: if [Index]=1 then null else List.Range(Source[Value], [Index]-2,1))

or use try [code] otherwise null

Step 2: Click on the double arrow of the Lag1 column > Expand to new rows.

This will generate the previous row value. If you need to calculate the Net Change simply add a Custom column and the formula will be: [Value]-[Lag1]

To access a row at a offset (-7) use:

try

List.Range(Source[Value], [Index]-8,1)

otherwise null

(Power Query is based zero.)

Step 3: Lead(1) This returns the value from the next row in order and the null value will be on the bottom.

Lead1=try

List.Range(Source[Value], [Index],1)

otherwise null

Step 4: Lead(7) If we need to return the 7th row.

Lead7=try

List.Range(Source[Value], [Index]+5,1)

otherwise null

Method 2

Lag(1)

Step 1: Add a Custom Index starting at -1 , increment 1

Step 2: Add a Custom column referencing the last step from the applied step panel. This will return whole table for each row.

Step 3: using positional operator { } and lookup column operator [ ]

Lag1=AddedIndex{[Index]}

Using column Index as a positional operator returns previous record for every single row.

You can specify which column from the record you want to return by using the field access operator.

Lag1=AddedIndex{[Index]}[Value]

To calculate the Net change:

NetChange= try

[Value] - AddedIndex{[Index]}[Value]

otherwise null