📄️ 3.1 | Chapter overview
In this chapter we will build the automated data preparation process for the demo sales dataset with Power Query. We will first connect Power BI with the Excel-file containing the demo data. Following that, we will first prepare the dimension tables and then the fact tables step-by-step. At the end of this chapter, we will tidy up Power Query and load the data tables to the data model.
📄️ 3.2 | Connecting Power BI with the example data Excel file
With the following steps, we create a new Power BI Desktop file (.pbix) and we connect with the Excel file containing the demo data (in multiple tabs).
📄️ 3.3 | Preparing the customer dimension (dimCustomer)
After connecting the Excel file and its data tables, we will now proceed with preparing the individual dimension and fact tables using data transformation steps in Power Query. The first dimension we will prepare is the customer dimension containing respective master data.
📄️ 3.4 | Preparing the product dimension (dimProduct)
In this chapter we continue with preparing the product dimension dimProduct. For this table, some transformation steps are the same as for dimCustomer (in general you will notice over time that there is a certain set of transformations that is relevant for almost all data tables). Some transformation steps are new and will broaden your understanding of Power Query.
📄️ 3.5 | Preparing the sales channel dimension (dimSalesChannel)
The sales channel dimension dimSalesChannel is only a small table and there are not many transformation steps required to prepare it for the data model. All the operations used have been shown before and thus I will keep this chapter short.
📄️ 3.6 | Preparing the date dimension (dimDate)
The final dimension table to be prepared is one that is likely shared among nearly all existing business intelligence solutions worldwide: the date dimension.
📄️ 3.7 | Preparing the sales fact table (factSales)
After preparing the dimension tables, we can now continue with the fact tables for the sales transactions and the sales forecast. Remember, a fact table contains transactional data telling us what has happened when. With the associated dimensions we can describe what has happened in the facts by filtering, slicing and aggregating fact data.
📄️ 3.8 | Preparing the sales forecast fact table (factForecast)
In the previous chapter we prepared the fact table factSales which contains the actual sales transactions that happened over time. In many real-world use-cases plan data is integrated in the BI solution in order to allow comparisons and better judge performance.
📄️ 3.9 | Organizing Power Query and loading data to the data model
In the previous chapters we prepared all dimension and fact tables. In this final chapter for data preparation, we want to tidy up Power Query and load the data to the data model.