Power Query: Previous Value

Welcome to our Power Query blog. Today, I am looking at two ways to reference a previous value in a column.

My imaginary salesperson, Mary, has been busy. I want to look at how her commission varies daily:

I upload my data into Power Query, using ‘From Table’ on the ‘Get & Transform’ section of the Data tab.

I am going to add an index column, from the ‘Add Column’ tab.

I choose to start from zero (0).

The step is called ‘Added Index’, which I need to remember for later.

I am going to add another index column, this time starting at one (1).

I am going to merge ‘Added Index’ and’ Added Index1’ by using some M code in a step. This will allow me to link each row to the previous row.

The M function I am going to use is Table.NestedJoin(), which I used in Power Query: Group Dynamics, when I was calculating group share.

The M code I have used is:

This takes my data with the index that begins at zero (0) and links it to the table with the index that begins at one (1).  Therefore, it links all the data to the previous row. 

Next, I need to expand some data from my joined table.

In this case, I will ‘Use original column name as prefix’, so I can distinguish between my original and expanded data.

I can now remove the row with null values; I choose to apply a filter.

Now, I create a custom column to calculate the daily difference in commission.

I click OK to see my new column.

I can track my salesperson’s progress in terms of commission and set targets if I wish.

I can expand this method to include data from other salespeople.

However, there is another way I can use to link to a previous value in a column which will speed up this process, without the need for merging with indexed tables. In this option, I start with my original uploaded data.

In the Advanced Editor, I amend the code from the original M code

to something a little more complex:

The M code I have used is:

This creates a column which links to the commission on the previous row.

Taking the M code line by line (I say, and then take two lines!):

This line gets my source from the workbook.

This converts the Commission column into a list, and puts a null at the top of the Commission column and removes the last entry, effectively creating a list of the previous Commission values.

This adds the new list to the list of columns already in my table.

This creates a table from my original columns, plus the new column which I have called Previous Commission.

The daily difference in commission can then be calculated in a new column.

I created this by selecting Commission, then Previous Commission and using a standard subtraction column on the ‘Add Column’ tab. I can then proceed as before to set targets if I wish.

Come back next time for more ways to use Power Query!