Build your Own Date Dimension for Power BI – Part 2

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:

1. Extract, Transform and Load the date data to Power Pivot
2. Structure the loaded data table into a Date dimension

Build your Own Date Dimension for Power BI – Part 1

The most common dimension used in a BI solution is the Date dimension. This is obvious since almost any type of analysis involves looking at data from some aspect of date; year, month, or even hours and minutes in some cases. In traditional BI scenarios, a Date dimension table is always created as a practice, as is an ETL to populate it. The usual case is a stored procedure which generates a range of dates between two given dates, and scheduled to run on a regular basis: yearly, quarterly etc.

In the case of a Power BI solution, one would not have the luxury of a date-generator stored procedure. Instead we could go for a purpose-created Excel file with all necessary fields with a range of dates. The issue with this would be that if you plan to use it across various disparate solutions, the Excel would have to be quite comprehensive with a large range of dates to support various scenarios. Locality will also have to be taken into consideration, such as a British calendar or a US one for example.