A Date table is needed for analysis in Power BI because it helps organize and analyze data by date. As the name implies, it enables you to create a calendar (date information) that works for the analysis you want to carry out. i.e weekday, month & year analysis.
Why is Date Table important in Power BI?
It can be used for a variety of purposes, including:
- Filtering data: It can filter data by specific date attributes, such as month, quarter, year, or weekday.
- Creating visuals: It helps in creating visuals with dynamic periods, such as the current quarter or month.
- Comparing data: It helps in comparing data over time, such as from
one year to the next or from one month to the next.
- Using DAX time intelligence functions: You can use DAX time
intelligence functions that wouldn’t normally work without a date
table.
- Optimizing data model: When working with large data sets, a separate
date table can help you establish associations between your data
tables and the date table. This can help you boost productivity,
cross-functional reporting, and optimize your data model.
- Standardizing date formatting: It can help create standardized date
formatting. Improving data modeling: It can improve data modeling.
You can check out BI Samurai for more explanation on why you need a date table
A date table can be created in Power Query or DAX. In this article, you will be creating your date table in Power Query.
Creating a Date Table in Power Query
Open Power Query, Home>>Transform Data
Click on the New Source dropdown and Create a blank query.
In the formula bar, write an M code to compute the date list.
= List.Dates(#date(2025,1,1), 365, #duration(1,0,0,0))
List.Dates(start as date, count as number, step as duration)
- List.Dates: the list.date function take 3 arguments (parameters)
- start as date: the date you want to start from
- count as number: the number you want it to count from the start date
- step as duration: the number of days, hours, minutes or seconds you
want to count from the start date.
In the code above, the start date is the 1st of January 2025, then count 365 of the duration arguments. The #duration arguments have (1,0,0,0) which translates to 1 day, 0 hour, 0 minute, and 0 second.
In this case, the date will start from 2025–01–01 and count 365 days from the start date, and that’s what will return in your query.
Once a list column has been created, right-click on the column and convert the list to a table (that’s what Power Query allows transformation on).
Leave delimiter as None. Then click OK
NB: if you have data that contains a list of items i.e. [dogs, cats, birds] etc. The delimiter in this scenario is the comma separating those items, and if there is a row that doesn’t have a list value, how it will handle it is the second option in the prompt box.
Now it’s in table format. (tabular structure)
Change the data type and rename the column, go to Add Column>>Date and start selecting other date parameters to form the calendar table.
NB: Make sure the Date column is selected before adding a new column.
You can get the scripts to replicate yours
let
Source = List.Dates(#date(2025,1,1), 365, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),
#"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Start of Year]), type date),
#"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Days in Month" = Table.AddColumn(#"Inserted Month Name", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
#"Inserted Start of Month" = Table.AddColumn(#"Inserted Days in Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
#"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
#"Inserted Quarter" = Table.AddColumn(#"Inserted End of Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
#"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),
AddColumns = Table.AddColumn(#"Inserted End of Quarter", "Month Name Short", each Date.ToText([Date], "MMM"), type text),
AddMonthAndDay = Table.AddColumn(AddColumns, "Month & Day", each Date.ToText([Date], "MMM") & " " & Text.From(Date.Day([Date])), type text),
AddMonthAndYear = Table.AddColumn(AddMonthAndDay, "Month & Year", each Date.ToText([Date], "MMM") & "-" & Date.ToText([Date], "yyyy"), type text),
AddYearMonthNum = Table.AddColumn(AddMonthAndYear, "Year Month Num", each Number.FromText(Date.ToText([Date], "yyyyMM")), Int64.Type),
AddYearMonthName = Table.AddColumn(AddYearMonthNum, "Year Month Name", each Date.ToText([Date], "yyyy-MMM"), type text),
#"Inserted Day" = Table.AddColumn(AddYearMonthName, "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Inserted Start of Day" = Table.AddColumn(#"Inserted Day of Year", "Start of Day", each Date.StartOfDay([Date]), type date),
#"Inserted End of Day" = Table.AddColumn(#"Inserted Start of Day", "End of Day", each Date.EndOfDay([Date]), type date),
#"Inserted Day Name" = Table.AddColumn(#"Inserted End of Day", "Day Name", each Date.DayOfWeekName([Date]), type text)
in
#"Inserted Day Name"
Happy Querying!!! Now you have a date table to use for your analysis.
If you encounter any difficulties or have any questions, feel free to reach out through the comment box. You can connect with me on Twitter and LinkedIn where I share my daily activities about data analytics, personal experience, and career life.
