CODEWITHSUNDEEP
excelpowerquery
PikeBishop
PikeBishop

Reputation: 89

How to look/peek at previous or next rows

Hi I'm new to power query and I'm stuck on an issue which i hope you can help - I have an audit report looking a records changing status and i want to be able to use the [Time Stamp] of the next record to calculate the datediff.

In Qlikview, i've used peek() and previous() and SQL, lead() and lag() but is there a function i can use in power query(M)

ID [Old Status] [New Status] [Time Stamp] [Duration] 1 NEW Estimated 6/1/2015
2 Estimated Planned 7/1/2015

Upvotes: 1

Views: 1881

Answers (1)

Carl Walsh
Carl Walsh

Reputation: 7009

A simple (maybe slow) solution is to add an index column, and then calculate the difference based on the column of the next/previous column.

let
    Source = OData.Feed("services.odata.org/V4/Northwind/Northwind.svc"),
    Orders_table = Source{[Name="Orders",Signature="table"]}[Data],
    SelectColumns = Table.SelectColumns(Orders_table,{"OrderID", "CustomerID", "OrderDate"}),
    Index = Table.AddIndexColumn(SelectColumns, "Index", 0, 1),
    Diff = Table.AddColumn(Index, "Custom", each [OrderDate] - Index{[Index]-1}[OrderDate])
in
    Diff

Upvotes: 2

Related Questions