Forum Discussion
Creating Date Tables
As of today (10/26/18), what's the most convenient way to create date tables in BI? Apparently there used to be a feature whereby one only had to click a single button to drop a date table into the model, but apparently it's been moved or disabled outright. Thanks for any feedback.
IMHO, the fastest way is:
- Open a blank query in Power Query of Power BI
- type ={Number.From(#date(2018,1,1))..Number.From(#date(2018,12,31))}
- That will generate a series of numbers as a list
- Convert it to a table (upper left menu button.
- Convert the ABC123 type to date
- Rename to Date.
- Now you have a date table. Add columns as necessary (year, month, month name, etc) to make your date table suit your needs.
- Close and load.
- Right-click on it and mark it as a date table.
If you have source data with dates in it, get fancy and find the earliest date in your data, then make row #2 above be Jan 1, YYYY where YYYY is the earliest date in your dataset,
So if your original line is like this, you just need to use some functions to determine the dates vs hardcoding.
={Number.From(#date(2018,1,1))..Number.From(#date(2018,12,31))}This will always give you a rolling 6 months. I've inserted a lot of line feeds to make the formulas a bit easier to read, but you could type that Source line all on one line. The key to all of this is DateTime.LocalNow() - that is equivalent to @NOW() in Excel - the current date and time from the system clock.
let Source = { Number.From( Date.AddMonths( DateTime.Date( DateTime.LocalNow() ), -6 ) ).. Number.From( DateTime.Date( DateTime.LocalNow() ) ) }, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}) in #"Changed Type"
26 Replies
IMHO, the fastest way is:
- Open a blank query in Power Query of Power BI
- type ={Number.From(#date(2018,1,1))..Number.From(#date(2018,12,31))}
- That will generate a series of numbers as a list
- Convert it to a table (upper left menu button.
- Convert the ABC123 type to date
- Rename to Date.
- Now you have a date table. Add columns as necessary (year, month, month name, etc) to make your date table suit your needs.
- Close and load.
- Right-click on it and mark it as a date table.
If you have source data with dates in it, get fancy and find the earliest date in your data, then make row #2 above be Jan 1, YYYY where YYYY is the earliest date in your dataset,
It will do automatic dates but you have little control over it. I 100% of the time disable the automatic date options in Power BI and roll my own date table.
Where in Excel 2016 can you do an automatic date table? Short of the CALENDARAUTO() DAX function. You could also do this in DAX with the CALENDAR() function, but I prefer to create my tables in Power Query. They are more compact. If you do it in DAX you have calculated columns, which I try to avoid.
And CALENDARAUTO() is very dangerous. If you have somethign like a marketing campaign table with bogus expiration dates like 12/31/2999, which is actually not rare, CALENDARAUTO() will create a full calender table 900 years out!
If you are talking about another way to do an automaticl calendar in Excel 2016, I'd be interested in hearing it.
edhans I've got a new adaptation I need.
I have created your mquery date list, but instead of hard coded dates, I want to bring in the previous 6 calendar months, up to and including today.
Go.
Jemma 🙂
So if your original line is like this, you just need to use some functions to determine the dates vs hardcoding.
={Number.From(#date(2018,1,1))..Number.From(#date(2018,12,31))}This will always give you a rolling 6 months. I've inserted a lot of line feeds to make the formulas a bit easier to read, but you could type that Source line all on one line. The key to all of this is DateTime.LocalNow() - that is equivalent to @NOW() in Excel - the current date and time from the system clock.
let Source = { Number.From( Date.AddMonths( DateTime.Date( DateTime.LocalNow() ), -6 ) ).. Number.From( DateTime.Date( DateTime.LocalNow() ) ) }, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}) in #"Changed Type"
Hi edhans
What do you means by (4) Convert it to a table (upper left menu button , this not clear senince could you eloberate it?
Hi,
You may go to Data > Modelling > New Table and enter this formula
=CALENDAR(MIN(Data[Date]),MAX(Data[Date]))
Hope this helps.
I just came across this and it's great, I wonder if I wanted to add 6 months before and after my chosen date (to widen the scope if I want to use another date in my dataset) - how to I go about this?
I tried the following which doesn't work:
Calendar = CALENDAR(MIN((GCRTDatabase[Received Date])-182),MAX((GCRTDatabase[Received Date])+182))Hi,
That New Table formula seems correct except for the bracketing
Calendar = CALENDAR(MIN(GCRTDatabase[Received Date])-182,MAX(GCRTDatabase[Received Date])+182)
Another way to create a date table using Power Query in Power BI is by using the following query. It will generate a list starting from September 1, 2017. You can replace the start date according to your needs and create a list of date numbers.
= {Number.From(#date(2017,9,1))..Number.From(Date.From(DateTime.LocalNow()))}
Creating a Date Table in Power BI Using Power Query
There are several ways to create a Date Table in Power BI. However, creating it using Power Query is the most efficient and robust approach.
I have developed an M Query formula that you can directly use. You only need to replace the minimum and maximum date source table names in the formula. That’s it — your Date Table will be ready.
Once you have the Date column, you can easily create other time-based columns such as Year, Month, Quarter, and Week from the Transform > Date section.
Steps to Quickly Create the Date Table
- Create a Blank Query
Go to:
Home → New Source → Blank Query - Rename the Blank Query to “DateTable”
In the Query Settings pane (on the right):
Name → type DateTable - Paste the M Query Code
Open the Formula Bar and paste the M Query code below.
Replace Source[Date] with your actual date column name (usually from your Fact Table).
Use the below M Query to Generate Date Tabele:
let
Source = fact_sales_monthly, // Replace with your actual table name
MinDate = Date.From(List.Min(Source[date])), // Replace 'date' with your actual date column name
MaxDate = Date.From(List.Max(Source[date])),
DateList = List.Dates(
MinDate,
Duration.Days(MaxDate - MinDate) + 1,
#duration(1, 0, 0, 0)
),
DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"})
in
DateTable- Create a Blank Query
