PowerBI Create a date table in Power Query (M)

In another blog post, I covered 'How to create a Date Table using DAX' which is one of the most common ways to create a date table in PowerBI. Another method that might be of interest is to create the table using Power Query. Anyone who has used Power Query will know that although it can achieve a similar output to DAX the process is very different.

In this post, I'll show you how to create a relatively basic Date Table in Power Query that will contain a list of dates and 5 additional columns with the dates associated with the end of the month, quarter, week, year and fortnight. The process is to create a list of dates and then add custom columns for each of the additional columns that we want to include in the date table

Although the most common way of creating a date table appears to be within DAX, I thought this would be an interesting exercise to see what is possible in Power Query (M). Date tables in DAX are expertly covered by the guys over at SQLBI in their article 'Creating a simple date table in DAX' which is well worth a read if you want to a more in-depth understanding of the same application in DAX. Additionally, this example covers the basics, I'll follow this up with a more in-depth article to cover more functional date tables.

To create the date table, I'm going to use some Date, Duration, Value and Number functions, specifically:

• Date.EndOfMonth
• Date.EndOfQuarter
• Date.EndOfWeek
• Date.EndOfYear
• Date.AddDays
• Duration.Days
• Value.Multiply
• Number.Abs

The first step is to select 'blank query' from the 'get data' drop down menu in the ribbon and enter create a list of dates by entering the following into the function bar. 

    = List.Dates(#date(2019, 01, 01), 365, #duration(1, 0, 0, 0)

This will create a list of dates starting from 1st of January, 2019 with 365 intervals of 1 day, ie. up to 1st of January 2020.

Next, the list needs to be converted to a table using the button in the ribbon. Once this is clicked, accepted the default options by clicking 'ok'. Once this has happened, we'll rename the change the column title from 'Column1' to 'Dates' to make it more useful. 

Next, we'll start building the additional columns by clicking on 'Add column' then 'Custom Column'. In the pop up window we'll enter the New Column Name (EndOfMonth) and the following formula in the 'Custom Column Formula' box.

     = Date.EndOfMonth([Dates])

Then repeat the process for the additional columns for End of Quarter...

=Date.EndOfQuarter([Dates])

End of Week...

= Date.EndOfWeek([Dates],2)

End of year...

=Date.EndOfYear([Dates])

and finally a slightly more complex column to derive the end of fortnight with a start date of 17th of December 1989

=Date.AddDays([Dates],14-(Duration.Days([Dates]-#date(1989, 12, 17))-Value.Multiply( 14,((Number.RoundUp(Number.Abs(        Duration.Days([Dates]-#date(1989, 12, 17))/14),0))-1))))

So that's the table built, now we need to add some finishing touches to make sure it works in the data model. The next step is to configure each of the columns as date type (not date and time!) and then the query will be complete. To change the type, select all columns, right click on one and select 'Change Type', then 'Date'.

The default query name will still be in place, so lets change this from Query1 to 'M Date Table' before we 'close and apply' the query editor.

Once that's done, click 'close and apply' to save and load the query into PowerBI. The last step that needs to be done is to configure the table as a date table by right-clicking on it, selecting 'make date table' and then selecting the 'dates' column as the primary date column.

So that completes the steps to create the date table in PowerQuery (M). Now it is in the model, calculated columns can be added using DAX to provide more functionality.

If you find the date table/time intelligence isn't quite working, check out another post I wrote recently '4 reasons why your time intelligence might not be working in Power BI' for some tips I've learnt along the way.

Any questions, get in touch!

https://twitter.com/eddierthomas

https://www.linkedin.com/in/eddierthomas/

Here is the code used to create the query in this example.

let

    Source = List.Dates(#date(2019,01,01),365,#duration(1,0,0,0)),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Dates"}}),

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "EndOfMonth", each Date.EndOfMonth([Dates])),

    #"Added Custom1" = Table.AddColumn(#"Added Custom", "EndOfQuarter", each Date.EndOfQuarter([Dates])),

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "EndOfWeek", each Date.EndOfWeek([Dates],2)),

    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "EndOfYear", each Date.EndOfYear([Dates])),

    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "EndOfFortNight", each Date.AddDays([Dates],14-(Duration.Days([Dates]-#date(1989, 12, 17))-Value.Multiply( 14,((Number.RoundUp(Number.Abs(        Duration.Days([Dates]-#date(1989, 12, 17))/14),0))-1))))),

    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom4",{{"Dates", type date}, {"EndOfMonth", type date}, {"EndOfQuarter", type date}, {"EndOfWeek", type date}, {"EndOfYear", type date}, {"EndOfFortNight", type date}})

in

    #"Changed Type"