Baton Rouge Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.

Previous Row Value in Power Query

By Andy Parkerson posted May 27, 2018 02:19 AM

  

Someone asked how he could get a calculated column with the value for a certain column in the previous row. In Excel, this is a simple task. However in M, things become a bit more tricky.

Suppose you are taking measurements, and for some reason you want the previous measurement to be stored with your data. There are optimization concerns that may make this a useful thing to do, as Power Query only performs its actions on refresh, and not once the data loaded. Alternatively, DAX does it’s work every time you select a slider, click a visualization, or basically change anything on the report.

This is one of the times when a good SQL background helps your M development.

One way to do this is to add an index on the table starting at 0, and another index starting at 1. Then merge (join) the query to itself on the first index and the second index.

Query of the previous row value
Here is the query that generated this ( I loaded the sample data from an Excel sheet):

let
 Source = Excel.Workbook(File.Contents("C:\Users\andyp_000\Desktop\PreviousValue.xlsx"), null, true),
 Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
 #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
 #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"date", type date}, {"measurement", Int64.Type}}),
 #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
 #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
 #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Added Index1",JoinKind.LeftOuter),
 #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"measurement"}, {"Added Index1.measurement"}),
 #"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"date", Order.Ascending}}),
 #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Added Index1.measurement", "Previous Row"}}),
 #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index", "Index.1"})
in
 #"Removed Columns"

Here is a matrix showing the results:

Matrix showing the previous row values

1 comment
17 views

Permalink

Comments

Feb 10, 2019 06:35 PM

Hi Andy and thanks for this insight. I came across your technique whilst I was working on a Power Query problem in Excel but I appreciate this is a Power BI group.

Anyway, as I worked through this technique in Excel I found that as I merged the Index columns, Query had automatically created two Queries for me, the one I started with and the new, merged, Query.

As far as I can tell, I cannot create a connection only Query for the first, which means that I end up with two queries that are both separate and linked!

Do you know of any way I can suppress/hide the original Query and leave my merged Query alone?

Best wishes and thanks for your time and efforts.

Duncan