In this short tutorial I will go through the process of getting data into Power BI and creating a data model in six simple steps. I make use of a demo data Excel file of a company selling goods in various locations in Denmark.
Step 1: Get data from Excel
There are three main ways to import data in Power BI Desktop.
The first is by clicking on the Import data from the Excel button in the middle of the canvas.
The second is by clicking on the Get data button on the ribbon or by clicking on the Excel workbook button right beside it.
The third is by clicking on the Transform data button which will open Power Query.
In the Power Query Editor, on the top left, there is the New Source button and by clicking on it, a dropdown menu will appear with the most common data sources including Excel.
Step 2: Select the right Worksheet or Table
After clicking one of the three buttons outlined above, the Navigator window will open. Here it’s possible to bring in the whole worksheet or separate tables. As a best practice, it is always better to format the data as tables before importing it into Power BI especially when working from an Excel file.
The way to differentiate between worksheets and tables is by checking the icons: blue for tables and white for worksheets. I also renamed the tables in Excel by adding an underscore and the word “table”. In this case I go ahead and put a check mark on all the tables that I want to import and click on Transform Data.
Step 3: Power Query Editor and basic Data Transformations
In the left pane of the Power Query Editor under Queries, I can see the five tables I just imported. On the right pane under Applied Steps, I can see the different data transformations steps applied to a particular table. One of the most important initial data transformations to be made is ensuring that the correct data type is set for each column.
Starting with the Customers table, I go ahead and click on it to see the columns it is comprised of. To change the data type I can click on the icon in the column header as shown below.
Another way of changing the data type is by navigating to the ribbon and in the Transform section, click on the Data Type button and choose the appropriate data type for each column.
Step 4: Arranging queries
It is a best practice to group the queries in distinct folders in the Queries pane. I usually group them by Fact tables and Dimension tables, Supporting tables etc.
To create a group simply select the tables by holding the Ctrl key and then right click and choose New Group.
Step 5: Rename the tables
There is also a clever idea to rename the tables as they get referenced when writing DAX formulas.
Power BI comes with IntelliSense, which is very intuitive, and with it it’s quite easy to identify the table to which the formula or function refers to. I find it helpful to give the tables a name that will immediately help me identify which table I am referencing. To rename a table, simply double click on the left pane under Queries and type in the appropriate name.
Step 6: Create a Data Model
Once all the above steps have been applied, click on the Close & Apply button in the top left corner of the ribbon. This will load the data and Power BI will automatically create relationships between the tables. However, it is always a good idea to check that the correct relationships have been made.
To navigate to the Model canvas, click on the Model icon on the top left of the canvas. It is the last icon on the bottom. The first two are the Report and Data icons.
In the Model canvas, we can already see the relationships between the tables that Power BI automatically created.
It is also possible to manually move and place the tables on the canvas. The most popular layouts are either by placing the fact table in the middle, in this case the Sales table and all the dimension tables around it or by placing the dimension tables on top and the fact table below. Both methods bare the name of a Star Schema whereby all the dimensions are connected directly on a one-to-many relationship to the fact table.
