1. Introduction
One of the unique features of Power BI is the Model view, which allows users to create relationships between tables. In this example, we have four Excel files called list of countries, Income, life expectancy, and population_total. Let’s explore how we can use this feature to create a data model.
2. The solution
We connect to these files as previously demonstrated by selecting the Excel Workbook icon in Power BI.
Once connected to the files, we move to the Model view, where we see the four tables.
We can build a data model by establishing a relationship based on the country name. In the list of countries table, the country name is called “name” and in the other three files, the country name is called “country”. To build the model, simply select and drag “name” from the list of countries table to the “country” in the other three tables. Now, we have established a one-to-many relationship between the list of countries table to the other three tables, which are shown as the 1 to * icons.
This means the table with 1 has unique values, while the table with * has many occurrences of this field. In other words, in the list of country tables, each country name appears once, while in the other tables, the country name can appear multiple times.
Just a general tip, it is recommended to arrange the tables, so the look-up table with the unique field is placed at the top, and the other tables are arranged at the bottom. This makes it easier to see the order of the table and the direction of the arrows, in this example the direction of the arrow is pointing towards the three tables, meaning the unique country name from the list of countries table can be used to filter the data in the other tables.