Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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.

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

IMHO, the fastest way is:

  1. Open a blank query in Power Query of Power BI
  2. type ={Number.From(#date(2018,1,1))..Number.From(#date(2018,12,31))}
  3. That will generate a series of numbers as a list
  4. Convert it to a table (upper left menu button.
  5. Convert the ABC123 type to date
  6. Rename to Date.
  7. Now you have a date table. Add columns as necessary (year, month, month name, etc) to make your date table suit your needs.
  8. Close and load.
  9. 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,



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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"

  



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

25 REPLIES 25
Arjit_Sharma
New Member

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()))}

Ashish_Mathur
Super User
Super User

Hi,

 

You may go to Data > Modelling > New Table and enter this formula

 

=CALENDAR(MIN(Data[Date]),MAX(Data[Date]))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur 

 

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)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur 


That worked perfectly thanks! I did play with the brackets a little but clearly didnt get it quite right 🙂

You're a star!

You are welcome.  Thank you for your kind words.  If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
edhans
Super User
Super User

IMHO, the fastest way is:

  1. Open a blank query in Power Query of Power BI
  2. type ={Number.From(#date(2018,1,1))..Number.From(#date(2018,12,31))}
  3. That will generate a series of numbers as a list
  4. Convert it to a table (upper left menu button.
  5. Convert the ABC123 type to date
  6. Rename to Date.
  7. Now you have a date table. Add columns as necessary (year, month, month name, etc) to make your date table suit your needs.
  8. Close and load.
  9. 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,



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

I have done this precisely as described above, but when I mark it as date table in the main report I am no longer able to create a Date hierarchy. What am I doing wrong? 

right-click on a date field and select Create Hierarchy, then right-click on a second date field and add to that hierarchy, and so on. 

edhans_0-1671027969194.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Ah right, ok. 

But why does making a Field the date table remove the date hierarchy?

Shouldn't. Turning off "Automatic Date/Time Intelligence" does turn off the automatic hierarchy, and that is a good thing. You do NOT want a date hierarchy (which is a hidden date table) for every date field in your model. Build your own hierarchies as needed - which don't create hidden tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Signed in to say thank you!

Hi @edhans 

 

What do you means by "4. Convert it to a table (upper left menu button." please eloberate it is not clear? 

The first 3 steps generate a List, not a table, so it looks like this:

edhans_0-1622132332771.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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? 

Anonymous
Not applicable

@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"

  



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

@edhans thanks for getting back to me.

This is almost perfect. I need the full calendar six months, not a rolling 6 months. So I want it to work out that it should start on the 1st September, not 26th September. Is that possible?

Jemma

Yes. Just wrap that result with Date.StartOfMonth() and it will go back to Sept 1. So it would be Date.StartOfMonth(Date.AddMonths(....



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Thank you so much for your help @edhans this has worked for me! 🙂 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.