Power BI – The Data Model – Using Relationships

How to Use Relationships

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 the last few lessons we’ve talked about what relationships are and a little about how they work but it’s all been quite theoretical.  In this lesson though we’re going to actually have a first look at what we can do now we’ve created our relationship and in this lesson we’ll demonstrate how we can use a field from one dataset to manipulate another dataset.

Ok we’ve got our Power BI file open on the Model View and it’s where we left off in the last lesson. Now let’s change this to the Report View where we’ve got a blank screen. So we’re going to create a quick visualisation (although of course we’ll go through visualisations in more detail as part of Stage 3 of the course) and what we’ll do is go to Visualisations here and add in a Table visualisation.

Now we can see over here on the righthand side that we’ve got the fields pane showing the four datasets attached to this data model. Now first of all I’m going to recreate part of the Retail Analysis dataset, by just bringing in the ItemID, and number of sales at the normal price made . And then I’m going to enlarge the visualisation just by going to the corners and dragging the visualisation outward. Ok of course this hasn’t got us any further than our original Retail Analysis report.

Now what I’m going to Do is remove the ItemID field from the report and then add in the ItemID field from the Item Dataset. And now you can see that the ItemID field has been added in and the report is exactly the same as it was when we had the Item ID field in from the Retail Analysis table.

Now that might not seem that exciting.

Afterall all we’ve done is taken out a field and replaced it with exactly the same field, but that same field is from an entirely separate report! And that’s the beauty of this ability to create relationships. Once the relationship is created you can take any field from either report and put them together in the same report as if they had been part of one dataset. In other words you can now use the fields from one dataset to manipulate another.

Now to demonstrate that, let’s say that we wanted to bring in the Category of the Items that we’ve sold but not have the ItemID in, so that we can see how many unit sales we’ve made in each category. Of course, our retail analysis dataset doesn’t have a category field, so we’ve got to rely on the category information from the Item dataset.

I’ll take off ItemID from the visualisation and instead put on Category. And there we go we’ve now summed the total amount of sales made by category in the retail analysis dataset. If we wanted to put in segment as well, then that’s fine, we can just drag and drop that in as well. And to make this more readable I could even flip this into a matrix table, where I can go up and down through the hierarchy of Category and Segment. But now we’re getting ahead of ourselves as we’ll be looking at visualisations in Section 3.

So you see how easy this is? Now the relationship is created I can bring in any field that I wish from either report and they’ll mesh together no problem. Now we’ll see this more, later in Stage 3 but we can employ the same tactic in any visualisation – our tables are now connected together and are like one big table of data!


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