Power BI – The Data Model – Row Context

What is Row Context?

Previous Session Summary

In the previous lesson, we covered how to enter the PowerQuery editor for the first time by highting some Purchase Order Data in our current workbook and then going to the Data tab and then going to From Table/Range.
 
We then looked at the different components of the PowerQuery editor being the Preview Screen, the Query Pane, the Tabs and Ribbon and the Query Settings Pane.

Lesson Synopsis

In this lesson we start looking at some of the basic steps that we or PowerQuery automatically use to transform our source data from its original ‘dirty’ state, to clean columnar data. Now before we start off in this lesson, one very simple, important point:
 
You cannot edit individual cells in the PowerQuery Editor. You can only perform transformations on whole columns, rows or tables.
 
In this lesson then we’re going to continue working with the PO data from the last lesson. We’re going to look at a couple of transformation steps which PowerQuery has added automatically and then we’re going to put in a few of our own, including removing some unnecessary rows, promoting the top row into the Header row and then loading our nice, neat columnar data into Excel.

Resource Listing

The Video

Play Video

Lesson Notes

In this lesson we’re going to learn about the concept of row context, which along with filter context, make up a measure or calculated column’s evaluation context –  the overall context in which an calculated column expression or a measure expression is calculated.

So let’s start off with the basics – what is row context?

Row context can be thought of as “the current row” when performing a calculated column or certain measures.  Essentially, what information is in the current row in the columns that I’m using to calculate the calculation that I’m currently performing.

So Why do we need to consider row context? Well we need to consider row context because Power BI does not reference individual cells in its calculations. Instead calculations, both in calculated columns and in measures reference tables and columns instead. Row context needs to exist to allow Power BI to process row by row calculations. When we go through this, again, this is a concept that might seem really obvious but it’s worth doing the example and solidifying it so that there’s no confusion in later lessons.

So, let’s look back at the calculated columns we created several lessons ago, calculating cost from our Gross Margin and price columns.

Open 2018 Retail Analysis file.

Here we are, here’s the 2018 Retail Analysis file and we have here our Cost_GBP column. Now let’s look at the formula we used to create this column. We can get this by selecting the Cost_GBP field in the fields pane.

Here’s the formula. Now as we can see, the formula is made up of Table reference so ‘2018 Retail Analysis Fact Table’ and then the relevant column names which are Price_GBP and GrossMarginAmount. So how does Power BI know what numbers it needs to take from these columns to calculate the cost column over here? If we were in Excel, in ranges there are individual cell coordinates, which tell Excel exactly which cells it needs to use to calculate formula, but here there aren’t.

The answer is row context. When Power BI receives a calculated column instruction, it will go down the calculated column row by row, taking the information from the columns we define in the calculation and that are in the same row that the calculation to calculate the result in each row. This is row context.

To look at it another way, row context actually exists in some parts of Excel, so we’re going to go back into Excel to demonstrate row context in a different way.

Cut to file Row context comparison

So here we are in an excel files and we have two ranges side by side which show the same bit of data – a section of the 2018 Retail Analysis File. On the left hand side we have the data formatted as a properly formatted Excel Table and on the right hand side, we have the same information as an unformatted Excel Range.

So let’s do our cost column again in our Excel Range table. There we go and as we can see, as we scroll down the column, that in the formula bar, the formulas are made up of cell coordinates – not table names or column names.

Now if we do the same calculation in the same way in our Excel formatted table…. There we are, the table column calculates automatically, without the need to copy the formula to all the cells in the column. The reason for this is that there are no cell coordinates in the columns. Here we go, if we scroll down this column, then the formula is the same and this is because Excel is employing row context.

Excel is moving down the column and then in each row it takes the information on that row from the columns in the formula to calculate the number that it returns in the calculated column.

For example in this top row, Excel says ok the formula for this column is Price minus margin so I need to go across to the Price column on this row, find the number then go across to the margin column and find the number and the subtract one from the other and return it in this row. I’ll then go down a row and do the same calculation with the numbers on this row and so on.

This is row context at work and Power BI row context works in the same way.

 

.... and a little more!

The Formula Bar – The Formula Bar does not automatically come up in the PowerQuery Editor the first time that you enter the Editor in Excel. To bring the Formula Bar up, go to the View tab. In the Layout section of the Ribbon, tick the Formula Bar box.

Remove rows – While we remove the unnecessary top rows in the video, many system reports also have totals lines at the bottom of the reports which can cause a problem when we’re doing things like summing our data later once we’re finished in PowerQuery. We also have the option to remove a specific number of bottom rows using the Remove Rows drop down in the Home tab, as well as removing alternative rows, duplicate rows, rows with errors and blank rows.

Use First Row as Headers – In the video we promote the first row in the table into the headers row. However we can also take the Headers and put them into the first row. To do this go to the same Use First Row as Headers drop down in the Home or Transform tab and select Use Headers as First Row.