Previous Row Value (Lag) and Next Row Value (Lead) in Power Query M

Adds columns showing the previous row's and next row's value for a given column, in row order.

Table TransformationsIntermediatelagleadprevious rownext row

Power Query M code

let
    Source = YourTable,
    Indexed = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    ValuesList = Indexed[Value],
    AddedPrevious = Table.AddColumn(Indexed, "PreviousValue", each if [Index] = 0 then null else ValuesList{[Index] - 1}),
    AddedNext = Table.AddColumn(AddedPrevious, "NextValue", each if [Index] = List.Count(ValuesList) - 1 then null else ValuesList{[Index] + 1})
in
    AddedNext

Notes

The table must already be sorted in the order you want lag/lead calculated in - Power Query has no inherent row order.

Requirements

A table already sorted in a meaningful order, with a column to shift.

How to use this snippet

In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Previous Row Value (Lag) and Next Row Value (Lead).