Power BI – The Data Model – The Data View

The Data View

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

After a high level introduction on how to create a data model and then then an introduction to  our dataset in lesson 2 we’re going to have a proper look at the modelling views available in Power BI and what each of the different functions do.

So at the moment our file looks pretty blank, but let’s look on the left hand side of this screen:

We can see that we have three tabs here:

1)     The Report View. This is where we currently are and eventually will show all of our pretty visualisations with all of our wonderful data that will tell the story of what’s going on in our dataset. This is where we’re going to work in Stage 3 – the visualisation phase.

 2)     The Data View. Let’s click into this. Ok here we go, this shows us all of the data of any reports that we’ve connected to either using the Enter Data function where we can directly enter data into Power BI here or the Get Data function. On the right hand side of the screen, we see the Fields of all of the available reports, all the reports that we’ve just connected to. Currently we’ve got all our four reports here, and whenever we connect to a new report then it should show up here unless we’ve asked for it not to. We can select different reports by selecting on them on the right hand side and they will come up in the main data preview section here and we can see that there is a representation of each column on the right hand side, here in the preview screen.  This screen is where we’re going to do most of our work in this Stage. 

3)     Finally we have the Model View. This tab shows a birds eye view of all the reports in the model, their fields and the relationships between the reports that we set (don’t worry, we’re going to get to relationships!). Over here on the right hand side of the screen we have the Fields pane which shows us the fields of the report that we’ve currently selected. This can be very useful as when we have large reports with lots of columns, then there’s not enough space in the report icon for all of the fields, so the fields can be viewed in the field pane. We also have the properties pane which dictates the name of the report. You can also put in a description of the report. This can be particularly useful for perhaps dictating the system and filters required for the report to be correctly run.

Finally we have this synonyms section which is useful for the Q and A function. Now the Q and A function can be used to ask Power BI questions about the datasets in plain English and Power BI will respond with answers. The Q and A function matches words in the question being posed with the headers of the columns, but in many cases there may be a variety of words meaning the same as our header that our report consumers might use instead of the header titles. Here we can put a list of words that might be synonyms for our column titles to aid Q&A’s performance. So Store could be Shop, SKU could be product, Day could be Weekday and so on.

Ok let’s go back to the data View and look in the Home Tab. Now along the ribbon at the top of the screen we have a number of available options. The options Get Data, Enter Data, recent sources and Refresh we should already be familiar with from Stage 1, while the Edit Queries button allows us to get back into our Queries to edit them if we wish to.

The Insert, Custom Visuals and Themes sections on the ribbon are for when we get to Stage 3 as these are all to do with creating the visualisations themselves so we won’t worry about them yet. Next to them we have the Relationships section, and a single button called Manage Relationships. Now this is the button that we’re going to use when we start to create relationships between our datasets. Just to let you know, this option will be greyed out if you only have one dataset, or you’re using a live connection model.

Next to that we have options to create new measures, new columns or new quick measures and we’ll get to these in due course. Finally we have the option to publish the report and this we’ll use in Stage 4, publishing our report from Power BI desktop to Power BI Service so that it can be consumed by our users.

Now if we move onto the Modelling tab, we again have our relationships section as well as some options for creating calculations. In our available calculations options we also have a new offering which is called New table. This is another method by which we can enter a table into the data model using the second of Power BI’s codes: DAX.

The remaining buttons in the modelling tab are better explained by examples so we’lll get to these throughout Stages 2, 3 and 4.

Ok, the first thing I’d like us to focus on next is creating relationships between our datasets so we’re going to look at arranging our first relationship in the next lesson.

.... 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.