# Generating A Date Dimension Table In Power Query

There are hundreds of different methods for generating a Date dimension table for use in Power Pivot: you can use TSQL, Excel formulas, import from the DateStream table in the Windows Azure Marketplace, and there’s even an app for it. Nevertheless I thought it would be a good idea to solve this problem in Power Query because:

• It seems like the natural tool to use, and a date table is one of the first tables a data steward will want to share with his/her users.
• It’s a very good way to learn about and demonstrate all the date functionality in M and Power Query. The more M code examples that there are out there, the better, right?
• Having just signed the contract to write a whole book on Power Query I need to master M very quickly!

Here’s how I did it. First of all, I decided to implement a function to create the table rather than a regular query; this function takes two parameters: the start date and the end date of the range of dates to appear in the table. Here’s the function I came up with:

`let`

`    CreateDateTable = (StartDate, EndDate) =>`

`let`

`    /*StartDate=#date(2012,1,1),`

`    EndDate=#date(2013,12,31),*/`

`    //Create lists of month and day names for use later on`

`    MonthList = {"January", "February", "March", "April", "May", "June"`

`                 , "July", "August", "September", "October", "November", "December"},`

`    DayList = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"},`

`    //Find the number of days between the end date and the start date`

`    NumberOfDates = Duration.Days(EndDate-StartDate)+1,`

`    //Generate a continuous list of dates from the start date to the end date`

`    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),`

`    //Turn this list into a table`

`    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}`

`                     , null, ExtraValues.Error),`

`    //Caste the single column in the table to type date`

`    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),`

`    //Add custom columns for day of month, month number, year`

`    DayOfMonth = Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])),`

`    MonthNumber = Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])),`

`    Year = Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])),`

`    DayOfWeekNumber = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1),`

`    //Since Power Query doesn't have functions to return day or month names, `

`    //use the lists created earlier for this`

`    MonthName = Table.AddColumn(DayOfWeekNumber, "MonthName", each MonthList{[MonthNumberOfYear]-1}),`

`    DayName = Table.AddColumn(MonthName, "DayName", each DayList{[DayOfWeekNumber]-1}),`

`    //Add a column that returns true if the date on rows is the current date`

`    IsToday = Table.AddColumn(DayName, "IsToday", each Date.IsInCurrentDay([Date]))`

`in`

`    IsToday`

`in`

`    CreateDateTable`

Some things to point out:

• Look – comments! Both multi-line /* */ and single line //. Adding comments to complex Power Query queries and functions is of course a Good Thing and something we should all be doing.
• The way I created the table from the start and end date is quite interesting: the List.Dates() function allows you to generate a list of dates from one date to another; I was then able to take this list and turn it into a table with Table.FromList().
• Adding extra columns to the table for the year and month number and so on is fairly straightforward. However, as far as I can see, Power Query does not have the ability to return month or day names (as opposed to numbers) from a date, so to get round this I created two lists called MonthList and DayList containing the months of the year and the days of the week, in order, took the day and month numbers and used these to find the name at the nth position in the list (see the MonthName and DayName steps).
• Date.IsInCurrentDay() compares a date with the current system date and allows you to see if a date is today’s date. There are a whole bunch of other interesting functions like this such as Date.IsInPreviousWeek(), Date.IsInCurrentYear() and so on, useful for creating relative date dimension tables like the one I describe here.

With the function created it’s very easy to generate a table using it. For example, I created a parameter table with start date and end date columns like so:

I then used this table as the starting point for a new query that called my function:

`let`

`    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],`

`    ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}`

`                  , {"End Date", type date}}),`

`    CallCreateDateTable = CreateDateTable(ChangedType[Start Date]{0}`

`                          ,ChangedType[End Date]{0})`

`in`

`    CallCreateDateTable`

Here’s the output:

You can download my demo workbook with all the code in from here.

PS If you haven’t downloaded the November update for Power Query already, I recommend you do so – the UI has been improved a lot.

### 51 responses

1. ruve1k says:

Did you intend to leave a link in your last bullet?
http://cwebbbi.wordpress.com/2013/01/24/building-relative-date-reports-in-powerpivot/

Also, Chris, do you have any resources about the full list of file properties/attributes that can be queried via PowerQuery?
In particular, I am currently running a query that returns a list of files in a folder (directory) and a number of properties related to those files, such as Date Created, Date Modified, File Size, etc.
I would like to retrieve the “Last Modified By” or “Owner” property of those files. Any help would be much appreciated.

• Yes, I did forget that link, thanks!

Sorry, I don’t have a list of file properties… I only have the Language and Library Specification documents.

2. Steven Peters says:

Hi Chris

Great news that you are writing a Powerquery book – I can’t wait.

I have also looked at creating a date dimension that looks similar to yours. I have also combined it with an external calendar website called http://www.timeanddate.com/holidays/uk/ to automatically append UK holidays to it. Here is my code – inevitably not as well written as yours

let paramquery = (mystartdate as date, myenddate as date) =>
let
mydays = Number.From(myenddate) – Number.From(mystartdate) + 1 ,
myDates = List.Dates(mystartdate, mydays, Duration.From(1)),
TableFromList = Table.FromList(myDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ChangedType1 = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
RenamedColumns = Table.RenameColumns(ChangedType1,{{“Column1”, “FullDate”}}),
DayNumber = Table.AddColumn( RenamedColumns ,” DayNumber “,each Date.Day([FullDate])),
DayName = Table.AddColumn(DayNumber, “#(tab)DayName#(tab)”, each Date.ToText([FullDate],” dddd “)),
DayAbbrev = Table.AddColumn( DayName ,” DayAbbrev “,each Date.ToText([FullDate],” ddd “)),
MonthNumber = Table.AddColumn( DayAbbrev ,” MonthNumber “,each Date.Month([FullDate])),
MonthName = Table.AddColumn( MonthNumber ,” MonthName “,each Date.ToText([FullDate],” MMMM “)),
MonthAbbrev = Table.AddColumn( MonthName ,” MonthAbbrev “,each Date.ToText([FullDate],” MMM “)),
YearNumber = Table.AddColumn( MonthAbbrev ,” YearNumber “,each Date.Year([FullDate])),
DateKey = Table.AddColumn( YearNumber ,” DateKey “,each Date.ToText([FullDate],” ddmmyyyy “)),
DayofWeek = Table.AddColumn( DateKey ,” DayOfweek “,each Date.DayOfWeek([FullDate],1)+1),
DayofYear = Table.AddColumn( DayofWeek ,” DayofYear “,each Date.DayOfYear([FullDate])),
DaysInMonth = Table.AddColumn( DayofYear ,” DaysInMonth “,each Date.DaysInMonth([FullDate])),
WeekOfMonth = Table.AddColumn( DaysInMonth ,” WeekOfMonth “,each Date.WeekOfMonth([FullDate])),
WeekOfYear = Table.AddColumn( WeekOfMonth ,” WeekOfYear “,each Date.WeekOfYear([FullDate])),
ReorderedColumns = Table.ReorderColumns(WeekOfYear,{“FullDate”, “#(tab)DateKey#(tab)”, “#(tab)DayNumber#(tab)”, “#(tab)DayName#(tab)”, “#(tab)DayAbbrev#(tab)”, “#(tab)MonthNumber#(tab)”, “#(tab)MonthName#(tab)”, “#(tab)MonthAbbrev#(tab)”, “#(tab)YearNumber#(tab)”, “#(tab)DayOfweek#(tab)”, “#(tab)DayofYear#(tab)”, “#(tab)DaysInMonth#(tab)”, “#(tab)WeekOfMonth#(tab)”, “#(tab)WeekOfYear#(tab)”}),
GroupedRows = Table.Group(ReorderedColumns, {“#(tab)YearNumber#(tab)”}, {{“Count”, each Table.RowCount(_), type number}}),
ChangedType2 = Table.TransformColumnTypes(GroupedRows,{{“#(tab)YearNumber#(tab)”, type text}}),
InsertedCustom = Table.AddColumn(ChangedType2, “Custom”, each DateHolidays([#”#(tab)YearNumber#(tab)”])),
RenamedColumns2 = Table.RenameColumns(InsertedCustom ,{{“#(tab)YearNumber#(tab)”, “YearNumber2″}}),
#”Expand Custom” = Table.ExpandTableColumn(RenamedColumns2, “Custom”, {“Holiday name”, “Holiday type”, “Where it is observed”, “Custom”}, {“Custom.Holiday name”, “Custom.Holiday type”, “Custom.Where it is observed”, “Custom.Custom”}),
EndTable = Table.Join(ReorderedColumns, “FullDate”, #”Expand Custom”, “Custom.Custom”, JoinKind.LeftOuter),
SortedRows = Table.Sort(EndTable,{{“FullDate”, Order.Ascending}}),
RemovedColumns = Table.RemoveColumns(SortedRows,{“YearNumber2”, “Count”, “Custom.Custom”}),
RenamedColumns1 = Table.RenameColumns(RemovedColumns,{{“Custom.Holiday name”, “Holiday name”}, {“Custom.Holiday type”, “Holiday type”}, {“Custom.Where it is observed”, “Where it is observed”}})

in
RenamedColumns1
in
paramquery

3. Steven Peters says:

Chris

Me again

You do not have to hard code names of month and days in list because you can use this

MonthName= Table.AddColumn(MonthNumber,”MonthName”,each Date.ToText([FullDate],”MMMM “)),

or this
DayName= Table.AddColumn(DayNumber, “#(tab)DayName#(tab)”, each Date.ToText([FullDate],”dddd”)),
DayAbbrev= Table.AddColumn(DayName,”DayAbbrev “,each Date.ToText([FullDate],”ddd “)),

• nt4boy says:

Thanks. The basic graphical interface brings up the Server and Table as
let
Source = Sql.Databases(“servername”),
speedus = Source{[Name=”databasename”]}[Data],
Tablename = datbasename{[Name=”Tablename”]}[Data]
in
Tablename

Or if I select an fxfunction in the database

GetComponentFromKey = databsename{[Name=”GetComponentFromKey”]}[Data]
in
GetComponentFromKey

Although I get a message “function (@key as nullable text as nullable number” with an Invoke button that I can press an enter an initial parameter.

But it doesn’t see the stored procedures in the database for me to select.
I guess I could just manually enter the Proc name in place of the function and see what goes.

4. Steven Peters says:

Just been working with a client on calling stored procedures and passing required parameters using Powerquery very easy very powerful. Syntax is same as you would use in management studio ie

EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 50;

you can change value of BusinessEntityID using PQ function capability

• nt4boy says:

Typically far simpler than I expected, and why is it like TSQL which is for me would have been the best solution, so least likely for me to guess at!
‘fraid I got frustrated and used the less friendly world of VBA so solve it this time, but thanks very much for the info kind sir.

5. Simona says:

Dear Chris,
Thank you for your usefull post. Where have you defined the “CreateDateTable” function in the excel workbook? I can’t find the function defininition anywhere.
Thank you!
Simona

• You need to have the latest version of Power Query installed – then go to the Power Query tab, click on the Workbook button in the Manage Queries section, and in the workbook queries pane you’ll see a query (in fact a function) called CreateDateTable.

• Simona says:

Thank you very much Chiris. It was my fault. I haven’t the the latest version of PQ installed.
Simona

6. Bellicose says:

Hi Chris,

Thanks for posting this, I’ve created my own dimDate table based on this, but I can’t figure out how to get IsInCurrentWeek & IsInPreviousWeek Logical TRUE/FALSE values based off of week that starts on Monday and ends in Sunday. I’ve created my own logic for it but it breaks down at the beginning and end of the year. What else can I do?

let
// Choose Start & End Date
StartDate=#date(2013,1,1),
EndDate=#date(2015,12,31),

// Calculate number of dates
NumberOfDates = Duration.Days(EndDate-StartDate),

//Generate a continuous list of dates from the start date to the end date
DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),

//Turn this list into a table
TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {“Date”}
, null, ExtraValues.Error),

//Caste the single column in the table to type date
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Date”, type date}}),

//Add custom columns for day of month, month number, year, etc etc
Day = Table.AddColumn(ChangedType, “Day”, each Date.Day([Date])),
DayDoubleDigit = Table.AddColumn(Day, “DayDoubleDigit”, each Date.ToText([Date],”dd”)),

MonthNumber = Table.AddColumn(DayDoubleDigit, “MonthNumber”, each Date.Month([Date])),
MonthName = Table.AddColumn(MonthNumber, “MonthName”, each Date.ToText([Date],”MMMM”)),
MonthNameShort = Table.AddColumn(MonthName, “MonthNameShort”, each Date.ToText([Date],”MMM”)),

Year = Table.AddColumn(MonthNameShort, “Year”, each Date.Year([Date])),

DayOfWeekNumber = Table.AddColumn(Year, “DayOfWeek”, each Date.DayOfWeek([Date])+1),
DayName = Table.AddColumn(DayOfWeekNumber, “DayName”, each Date.ToText([Date],”dddd”)),
DayNameShort = Table.AddColumn(DayName, “DayNameShort”, each Date.ToText([Date],”ddd”)),

IsToday = Table.AddColumn(DayNameShort, “IsToday”, each Date.IsInCurrentDay([Date])),
IsInCurrentWeek = Table.AddColumn(IsToday,”IsInCurrentWeek”,each Date.IsInCurrentWeek(Date.AddDays([Date],-1))),
IsInCurrentMonth= Table.AddColumn(IsInCurrentWeek, “IsInCurrentMonth”, each Date.IsInCurrentMonth([Date])),
IsInCurrentYear= Table.AddColumn(IsInCurrentMonth, “IsInCurrentYear”, each Date.IsInCurrentYear([Date])),

IsInPreviousWeek = Table.AddColumn(IsInCurrentYear,”IsInPreviousWeek”,each Date.IsInPreviousWeek(Date.AddDays([Date],-1))),
IsInPreviousMonth= Table.AddColumn(IsInPreviousWeek, “IsInPreviousMonth”, each Date.IsInPreviousMonth([Date])),
IsInPreviousYear= Table.AddColumn(IsInPreviousMonth, “IsInPreviousYear”, each Date.IsInPreviousYear([Date])),

SemiMonthDaySet = Table.AddColumn(IsInPreviousYear, “SemiMonthDaySet”, each (if [Day]<16 then "1-15" else Text.Combine({Text.From(16),Text.From(Date.Day(Date.EndOfMonth([Date])))},"-"))),

SemiMonthCurrentDaySet = Table.AddColumn(SemiMonthDaySet, "SemiMonthCurrentDaySet", each if Date.Day(DateTime.LocalNow()) < 16
then (if Date.IsInCurrentMonth([Date]) = Logical.FromText("True")
then (if Date.Day([Date]) 15
then “TRUE”
else “FALSE”)
else “FALSE”)),

SemiMonthPreviousDaySet = Table.AddColumn(SemiMonthCurrentDaySet, “SemiMonthPreviousDaySet”, each if Date.Day(DateTime.LocalNow()) 15
then “TRUE”
else “FALSE”)
else “FALSE”)
else (if Date.IsInCurrentMonth([Date]) = Logical.FromText(“True”)
then (if Date.Day([Date]) < 16
then "TRUE"
else "FALSE")
else "FALSE")),

QuarterOfYear= Table.AddColumn(SemiMonthPreviousDaySet, "QuarterOfYear",each Text.Combine({"Q", Text.From(Date.QuarterOfYear([Date]))}," ")),

WeekOfYear = Table.AddColumn(QuarterOfYear,"WeekOfYear",each Date.WeekOfYear(Date.AddDays([Date],-1))),
WeekNameOfYear =Table.AddColumn(WeekOfYear ,"WeekNameOfYear ",each Text.Combine({"Week",Text.PadStart(Text.From([WeekOfYear]),2,"0")}," "))

in
WeekNameOfYear

• Can you give me a few more details about what’s going wrong and what you’d like to happen please?

• Bellicose says:

I want the IsInCurrentWeek to start the week on Monday (like DAX function WEEKNUM(, 2)). Right now I’m calculating it like this in powerquery, Date.IsInCurrentWeek(Date.AddDays([Date],-1))), but the first day of the every year it shows like it’s last week of last year (week 53). Does the IsInCurrentWeek() have any return_type parameter like the DAX fucntion (So something like IsIncurrentWeek(,2)), I tried searching but couldn’t find any.

• Could you hack it by putting in a test that says if the current week number you’re calculating is 53 and the date is January 1st, then the actual week number is 1 (otherwise return the original week number you are calculating)?

• Bellicose says:

Thanks Chris, I did it the following way,

each if [Date] = Date.StartOfYear([Date]) then Date.IsInCurrentWeek([Date]) else Date.IsInCurrentWeek(Date.AddDays([Date],-1))),

7. chandan chauhan says:

Hi Chris,

I’ve posted this issue in powerquey forum already (link below) but I just wanted to ask if you’ve come across this. i’m using this DimDate table in many of the reports but after a recent update I’ve been getting out of line object error in many of them. Do you have any ideas what could be causing it. The screenshot and code is on the link below too.

http://social.technet.microsoft.com/Forums/en-US/41ff8e33-50fa-4196-a7f8-8f81416c28e2/trouble-refreshing-powerquery-built-datetime-tables-after-recent-update?forum=powerquery

• That looks like an installation problem. Do other Power Query queries work on the same machine?

• chandan chauhan says:

Others seem to work fine… the query works fine by itself.. but when it’s refreshed along with all other connections (powerpivot connections to an ODBC database) in a workbook.. it fails to refresh and gives the error..

• This sounds like a bug to me – I recommend you report it to Microsoft.

8. chandan chauhan says:

Hi Chris,

Just one minor adjustment to the query, I think the
DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0))
should be
DateList = List.Dates(StartDate, NumberOfDates+1, #duration(1, 0, 0, 0))
otherwise, it misses the last day of the year.

9. James says:

I have a table on an excel worksheet which includes a date column and has the cell values in 2 different visually represented formats; however, all the values in the date column in excel are set to “general”.

No cell value is formatted as “date” nor “number” in excel within the date column.

In the table, the dates are either displayed as 41425 or 31/05/2013. Both represent the same exact date.

When I import into Power Query and select the date column, it is automatically formatted as Data Type: Text.

If I select the entire column and select “Date” in Data Type drop down, the entire column errors out.

If I select the entire column, right click and go to Change Type > using locale, the entire column errors out.

How can I get the cell values in the Date column formatted to MM/DD/YYYY format in Power Query without altering the format/values in the data table which holds the raw data from our private server?

Thank you in advance!

• I think you need to split the table into two inside Power Query, each table containing one of the two formats, then in each convert the dates and then finally combine the two tables back into one.

This site uses Akismet to reduce spam. Learn how your comment data is processed.