Power Query is awesome – dynamic table of dates – #powerbi

Many blogposts has been written about how you can create a generic date table in your Power Pivot / Power BI datamodels.

Here is a few examples

https://blog.crossjoin.co.uk/2013/11/19/generating-a-date-dimension-table-in-power-query/

http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

https://www.powerquery.training/portfolio/dynamic-calendar-table/

But my challenge with these has always been that I had to specify from date and to date either in a worksheet, text file or directly in the Query Editor.

Normally your fact table actually contains the max and min date that you actually want your date table to contain – so in the last model I created I decided to see if I could make the from and to date dynamic based on the max and min date in the fact table.

By using a bit of M magic functions it turned out to be very easy.

Let’s begin

In my example I load the FactInternetSales table from the AdventureWorks database

Now as we can see in the query editor we have a column containing the Order date and the min and max of that column should be used to generate the table of dates.

Convert the OrderDate to Date instead of DateTime

Then add a blank query via the New Source button

In the formula bar you enter the following formula

= List.Dates(List.Min(FactInternetSales[OrderDate]),Duration.Days(List.Max(FactInternetSales[OrderDate])-List.Min(FactInternetSales[OrderDate])),#duration(1,0,0,0))

And you get a list of dates – 🙂

Now we can add columns with the date information we want in our date tableby using the add Column Date – From Date & Time

The date table now depends on the Query FactInternetSales – and with the September update of the Power BI Desktop we can see that via the Query dependencies window

Now Power Query apparently knows that it has to update FactInternetSales before it updates the Date table, so when our facttable is updated the Dates get updates afterwards.

You could by modifying the expression to get the list of dates calculate the first day of the min year and last date of the max year etc. – all this depends on how complete you want the datetable.

Power Query is AWESOME J

2 thoughts on “Power Query is awesome – dynamic table of dates – #powerbi

  1. Pingback: #Excel Super Links #21 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s