In my previous post I talked about and demoed how to get connected to a date data set on the Azure Marketplace. The purpose was to access date information to build a date dimension for a Power BI solution. In this post I will focus on how one would pull in data from the said data set through Power Query, massage it to suit my requirements and finally model it in Power Pivot for consumption.
To do this, let us make use of a simple scenario where I want a date dimension with Years, Months and Dates that go from Year 2000 to 2015. There are two major steps that I will perform:
- Extract, Transform and Load the date data to Power Pivot
- Structure the loaded data table into a Date dimension
The first step (ETL) uses Power Query to pull in data from the data feed before transforming it to Power Pivot. After connecting to the data set from the Azure Marketplace, I find that there are columns that I do not need, plus dates from the year 1900 till now. In order to get things into shape, I perform a few cleanup operations: Remove columns that I do not need, rename the fields to more friendlier names, change the data type of the date column to date instead of date/time, etc. Once done, the data is loaded to Power Pivot:
The next step happens within Power Pivot upon the loaded data. Here, I set the imported table as a Date table indicating the Date field as the key. I also hide a few fields such as MonthNumber and DayOfWeekNumber, since I do not want them being seen on the client side, and I only want them to be used to sort other fields such as Month and DayOfWeek. I also create a hierarchy using the Year, Month and Date columns:
And that’s it! You’ve got a Date dimension. Just make sure that any subsequent fact tables that are added have a date field that matches the format of the Date field on your Date dimension. While you can still connect to Azure Marketplace from Power Pivot and pull in the same data, using Power Query gives you more flexibility in massaging your data.