Easy Date Table in Power BI

So, one of the biggest hurdles I have experienced when working in Power BI with Marketing Dashboards, is the Date Table that translates all sources into being the same date. I have searched quite a bit for this, however, I stumbled upon this recently.

Nevertheless, I wanted to create a super easy approach that takes no time at all, and suddenly we have a working date table!

Follow these three easy steps to create a date table in Power BI.

1

Create Two New Parameters

2

Create a New Blank Query

3

Pate Code Into the Query Editor

Create Two New Parameters

In Power BI click Edit Queries. Here you click New Parameter as shown below.

After this we fill out the first parameter as StartYear. This marks the year that we want our date table to with.

As you might have guessed, we also need to make an EndYear parameter. So after clicked OK with the first, do the same thing again, but this time with a start date.

Be aware that the data type in both of them have to Decimal Number.

Create a New Blank Query

Now that we have our parameters in place, we will create a New Blank Query.

You will get this menu by right-clicking.

Choose Blank Query.

Pate Code Into the Query Editor

And now to the very last part!

When creating a new blank query, we do not get a very nice editor. That is why we will click Advanced Editor

After this, you will see a screen. Mark everything in the text field, and paste the code found in the on the page.

If everything is done correctly, you should have a table that looks like this:

The Code

let
    StartDate = #date(StartYear,1,1),
    EndDate = #date(EndYear,12,31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FullDateAlternateKey"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FullDateAlternateKey", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([FullDateAlternateKey]), type number),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([FullDateAlternateKey]), type number),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([FullDateAlternateKey]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([FullDateAlternateKey]), type number),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([FullDateAlternateKey]), type number),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([FullDateAlternateKey]), type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([FullDateAlternateKey]), type number),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([FullDateAlternateKey]), type number),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([FullDateAlternateKey]), type number),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([FullDateAlternateKey]), type text)
in
    #"Inserted Day Name"

 

I have made this post in order to share great work, and great work should be recognized. This article is inspired by Reza Rad’s very detailed blog post on the matter. For more information please visit his blog.
Want to know more? Reza Rad's Blog to Read More

Leave a Reply