Multiple tables into a single data model in power bi desktop we'll explain how you can get power bi to automatically detect relationships between those tables and if that doesn't work how you.
Can create and edit relationships manually we'll spend a bit of time talking about some basic data modeling jargon that you're likely to encounter when.
Discussing data models and we'll look at how relationships can affect filters in the visualizations in your reports in the final part of the video we'll get.
Into the dax side of things and talk about using the related functions to reference columns in related tables so let's get started to get started i've created a new report.
And as usual the first thing i'll do is import some data from an excel workbook just before i do that i want to talk about the structure of the data we're going to import because it's a little.
In this video we're going to import multiple different worksheets spread across a range of different excel files i'll drop a link in the video description so you can download these.
Files and follow along with the video if you'd like to i'm just going to have a quick look in the movies workbook which i've already got open here in excel down on the taskbar.
So this represents the main table of data we're going to import and it should look fairly familiar if you've followed along with previous parts of this series so far.
There are 1200 rows of data each row containing the details for one single film in previous videos we've stored things like the name of the director and the.
Name of the studio next to the film's details whereas in this video we've replaced the descriptive names with a simple id number.
So if we want to identify who the
Director of jurassic park is we'd have to look up the id number four in some kind of lookup table all the lookup tables for the id columns.We've got in this film table are held in the other tables workbook so i switch into the other tables workbook and find the director table we can look in the director id column for the director id.
Number four and then on that row we can look up any relevant details for that item the same is true for any of these other dimension tables if i have a look back.
At the movies workbook and find the genre column or genre id i can find that jurassic park has an id of number eight and if i switch back to the other tables workbook find the genre table look up.
The number eight in that table and then i can find that jurassic park is described as an adventure film this arrangement of data is a little unusual to find in an excel workbook but.
It is incredibly common to find this arrangement in a relational database or a data warehouse so we're going to work with this set of tables in our report and look at how we.
Can relate these tables together so let's head back to the power bi report and i'll choose to import data from the movies workbook to begin with and there's just a single worksheet in.
That workbook as we've seen so i can check the box next to the film table choose to load that data into the data model and then when that has happened we can.
Have a quick check in the data view that we've got the id numbers rather than the descriptive names and then let's have a look in the model view just before we start to import our other tables.
Next i'd like to import all of the other
Tables which contain the descriptive words related to these id numbers when i do that i'd like power bi to give me as much help as possible in.Automatically detecting the relationships between the tables we import and to try to force it to do that we can head up to the file menu choose options.
And settings and then in the options dialog box when it finally loads we can find the current file section and the data load page.
The check box labeled auto detect new relationships after data is loaded is the one we want to have checked and we can click ok and that will try to force power bi to detect relationships between.
The tables there are a couple of things that are important for the relationship to be detected correctly first of all the names of the columns.
Are quite important you can see in the film table i've got a genre id column and then if i look back at the genre table that we're about to import there's also a genre id column listed there.
So power bi will look for columns with the same name it's not entirely strict you can have different cases so it's not case sensitive and you can even have spaces.
In the words actually so if i separated out the genre and the id and maybe change the id capital letters to id lowercase letters that will still detect the relationship correctly i'll just.
Save the workbook there just to update that change one other thing that's very important is to make sure that at least one of the tables has unique values in the columns.
That you're going to create the relationships on so you can see here that in the genre table each genre id appears once and once only in this column.
Whereas of course in the film table there are going to be many western films and many drama films etc so these numbers are going to appear many times so back in the movies workbook we can.
See the genre id number eight adventure films appears lots of times for each adventure film having made those changes let's head back to power bi and then we can choose.
To import some more data from an excel workbook i'll go with the other tables workbook this time and then a quick little tip for you if.
You want to select multiple workbooks or multiple sheets from the same file while it's tempting to try to check the top box and then hold down the shift key.
And check the last box that always annoyingly auto unselects the last box so i'm going to hold down the shift key and uncheck the top box and a quicker way to do this if you click on the word.
Certificate the name of the top table and then hold down the shift key and click on the name of the last table that will select all the tables without unchecking that last box.
I can then click the load button to import all these new tables and power bi will try to detect relationships between the tables we're importing.
It makes a bit of a mess of the diagram initially that's fairly quick and easy to sort out by clicking on this reset layout button in the bottom right hand corner.
If i do that it will lay out all the tables we've imported correctly and it will show us that even though the names of the columns are slightly different between the genre table and the film.
Table there is still a relationship between those two columns when it comes to describing a data model there are several bits of jargon and terminology that you'll hear used.
Frequently that are well worth understanding to begin with we talked about the idea that in the genre table the genre id column contains unique values so each.
Genre id appears once and once only in the genre table whereas in the film table each genre id can appear multiple times this creates a relationship referred to.
As either a one to many or a many to one relationship depending on which perspective or direction you're talking about you can see the diagram indicates which.
Table sits at the one end of a relationship by showing a little one symbol at that end whereas the asterisk symbol on the other side indicates the many end at the relationship.
When you have an arrangement of tables like this when you have a single central table sitting in the middle of lots of many-to-one relationships so you can see film here has lots of many-to-one.
Relationships to all of our other tables this is referred to as a star schema so the tables sitting around the outside of the central table represent the points of the star.
The table which sits in the middle is referred to as the fact table whereas the tables which sit at the one end of the relationships around the outside are referred to as dimensions.
You'd use the dimension tables to group and filter and slice and pivot your data whereas information in the fact table is used to aggregate it so we'd see for example the average budget dollars.
Grouped by the genre of each film as a simple example your data models won't always be quite so clear-cut and simple as this one and in fact in this example we've got an.