Power Query – Get Previous Row Data

Last modified date

Occasionally I get asked a question via Twitter or LinkedIn that prompts me to write a post. The question asked was how to get previous row data. In this post I will describe how to get a value from the previous row that matches a criteria. The example data is peoples scores on different dates. I want to know if they are improving. For this I need their previous score.

Starting Data

Table of data with columns name, date and score

Getting Previous Row Data

The first step is to sort the data. You need the previous score to be in the previous row, so we sort by name and then date.

Data now sorted by Name and Date.

Then from the Add Column ribbon we add an Index column. And then a custom column, “Prev Index” which is Index-1

Custom Column dialog showing the calculation for Prev Index of Index -1

Now we can merge a table to itself (yes a touch head screwy but its fine it works) by linking Prev Index to Index. This though will give Bob Alice’s last score as his previous score so we add the Name column to the merge as well. Holding down the Ctrl key will allow you to select multiple columns and make sure at the bottom of the dialog you have some matching rows..

Merge dialog with columns selected for the merge

This creates a column that contains tables. Press the expand button and only select the Score column. Click OK to give a column of the previous scores ready to rename.

I then rename Score.1 to Previous Score and use Choose Columns to remove Index and Previous Index to end up with a table of name, date, score and previous score.

Final table including previous row data

Over 20 year experience at being passionate about training, solving problems and loving a new challenge especially in the Microsoft's Power Platform suite.