Home and Learn: Power BI 2023
Multiple Tables in Power BI
You can import more than one dataset in your Power BI dashboards. In fact, you can have as many as you like. One you have imported your first dataset, locate the Data panel on the Home ribbon:
From the Data panel, click the Get Data dropdown to see the list of file options again. If you're importing an Excel Workbook, there's a separate button for this option.
In the image below, the Data panel on the right of Power BI is showing two imported files via the Get Data option on the Data panel of the Home ribbon:
One of the files is the Pets Data CSV file you imported in an earlier lesson. The second file is an Excel Workbook, and is a list of Pet Shops. We won't be doing anything with this Pet Shops data, so you don't need to download it. (You'll get to download something shortly.)
If we were to click the Model View icon on the left of Power BI, we'd see this:
You'll be creating your own model soon. However, the thing to note here is that although both tables are showing up, there's nothing to connect the two of them - there's no relationship. The first table is a table about Pet Owners and their pets. The second table is just a list of Pet Shops and whether or not they sell a particular type of pet. So we have two separate tables with no way to link them. But you can still add dashboard visuals for both tables. It's just that Power BI can't do any filtering for both tables at the same time. It can filter either on the Pet Owner data OR on the Pet Shop data. But there's no way for Power BI to filter on, say, a pet type and where it was bought.
If the Pets Data table and the Pet Shop table both had a Pet Shop ID column, then you could link them on the ID column. But they don't, and Power BI can nothing about it. (You could add ID columns via Power Query.)
Typically, though, tables are linked. And once they are linked, Power BI can do a better job at filtering your tables. Let's see how it works.
Power BI Table Relationships
Download the following Excel spreadsheet (Right-click > Save As):
Start Power BI and click File > Get Data > Excel Workbook. Navigate to where on your computer you downloaded the Sales Data Widgets file. Open this file and you should see the Power BI Navigator:
Select all the sheets on the left and click the Load button at the bottom. Once Power BI has finished processing your files, expand the four tables in the Data panel on the right of Power BI: (If you can't see a Data panel, go to the View ribbon at the top of Power BI. From the View ribbon, toggle the Data item on the Show Panes panel.)
So we have four tables in the Data panel, each one a sheet from an Excel workbook. Let's see the data.
On the very left side of Power BI, there are three icons. These ones:
There is an icon for Report View, which is the Dashboard. There is an icon for Data View, where you can see the data going in to your tables. And there is an icon for Model View, which is used to view the relationships between tables.
Click the Data View icon. Whichever table you have selected in the Data panel on the right of Power Bi will be displayed. But click the Customers table:
(Notice there is a problem with the headers, which we'll fix shortly.)
Now double-click the other tables in the Data panel on the right, just to see what they look like. Here are all the tables:
They look a bit messy. But we have the sales data at the bottom. The three tables at the top are Widgets, WidgetPrices, and the Customers tables.
The thing to pay attention to here are all the ID columns. We'll use these to build relationships.
From the three icons on the left, click the one for Model View. You should then see this:
The four white rectangles represent a table from your Data panel. Power BI tries to make a guess at which columns in your tables are linked. But it hasn't guessed correctly for us. And we have the one orphan table bottom left, the Customers table. You can tell there's something wrong with this table as it has the default column names, Column 1, Column 2, Column 3. Let's fix that.
Right-click the Customers table in the Data panel on the right. From the menu that appears, select Edit Query:
When you select Edit Query, Power Query will launch. Make sure you are on the Home ribbon at the top. From the Home ribbon, locate the Transform panel and click the button that says, Use First Row as Headers:
Your table will change from this:
to this:
Also on the Home ribbon, click the Close & Apply button on the left. Power Query will close, and you will be taken back to Power Bi, which will update with the changes you made. Notice the Model View now. The Customers table has updated:
All those lines in Model View are guesses as to which columns in the tables are linked. It hasn't quite got everything right. To see what the issue is, you can rearrange your tables in Model View.
Left click anywhere on a table header. Hold down you left mouse button. Keep it held down and drag the table around. The red arrows in the image above show you where to click and drag.
A popular pattern is to arrange your main transaction table in the middle and place the other tables surrounding it. Like this:
This pattern, where your transactions are in the middle, is called a star schema. (The transactions table for us is where all our sales are.)
Another pattern is called the Collie schema, and has the transaction table at the bottom and the other tables at the top:
Now, the sales are at the bottom, and you have to 'look up' to see the other tables.
(Transaction tables are also known as a Fact Table, while the lookup tables are known as Dimension Tables.)
But our relationships are all wrong. There is a line linking the WidgetPrices table to the Sales table. Power BI has performed the link by matching the Product_ID column in both tables. What we'd like to do instead is to match the Product_ID in the Widgets table with the Product_ID in the Sales table.
We're trying to do this:
You can break a relationship quite easily. Right-click where the red X is in the image above. From the menu that appears, select Delete:
Power BI will ask if you're sure you want to delete this relationship. Click Yes. The lines between the WidgetPrices table and the Sales table will disappear:
To create a new relationship, drag the Products_ID item from the Widgets table onto the Product_ID table in the Sales table:
As soon as you let go of your left mouse button, you'll see a new line appear from Widgets to Sales:
There is a 1 under the Widgets table now, and an asterisk in the middle left of the Sales table. What you have done is to create what's known as a One to Many relationship between the two tables. This means that one record in the Widgets table (Red widget, Blue widget, etc) can have many entries in the Sales table. (A Red widget can be bought many times.) There is also a One to Many relationship between the Customers table and the Sales table. (One customer can buy many times.)
The other type of relationship is known as a One to One relationship. You can see this for the Widgets and WidgetPrices table - there is a little 1 in the top middle of each table. This means that one record in the Widgets table has exactly one entry in the WidgetsPrices table.
Adding a Power BI Table Visual
You may be wondering what the point of all this is, and isn't it a whole lot of trouble to go to? Well, the reason why you set up relationships is because of what you can do with a Power BI visual once the correct relationships are in place.
Switch back to Report View. Add a Table visual to your dashboard.
Click the Add data button. Expand the Customers table. Click
on Customer Name.
Now click the Add Data button again. This time, expand the Sales table. Select the Units Sold item:
Your table will then look like this:
Immediately, we can see how many widgets each customer bought. Effan, for example, bought the least at 5 widgets, while Trader Jack bought the most at 21 widgets. This information is coming from two different tables. And Power BI is only able to do this because of the relationship we set up. Think of how difficult that would be in Excel - you'd need to do some sort of VLOOKUP, referencing different sheets in your workbook.
Here's another table:
This time, we've selected Widget_Color from the Widgets table and Units_Sold from the Sales table.
Finally, here's a table that gets data from the Widget_Color table and the WidgetsPrices table:
Setting up relationships means that your datasets, like Excel spreadsheets, don't have to be one huge table with many columns. You can have lots of different sheets with fewer columns and let Power BI do all the work of building relationships between the tables - and no more VLOOKUPS!
In the next lesson below, you'll learn about image slicers.
<--Back to the Power BI Contents Page