Dynamic Date Table Using the Power Query Editor

In this tutorial video, we'll learn how to create a dynamic date table in Microsoft Power BI using the Power Query Editor. A date table is a fundamental component of many data models and can help us analyze and visualize data by date more efficiently.

-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - -
Next step on your journey:

Let's connect:


Pragmatic Works
7175 Hwy 17, Suite 2 Fleming Island, FL 32003
Phone: [phone]
#pragmaticworks

00:00 Purpose of a Date Table
02:22 Benefits of a Separate Date Table
03:29 Creating a Blank Query for Date Table
04:39 Generating a 3 Years Prior Column
06:47 Adding Start Date Column
08:21 Creating a List for Full Date Column

[ziggle]

NIce job. I liked your use of the sequence capabilities of Power Query. Also, you had the best explanation I have seen on why you need a date table in Power BI. Thanks!

[malgorzatamaciukiewicz]

Thank you do much for showing dynamic date filters.

[cyclemente]

This video is really useful, thank you so much!

[Milokraven]

Great video, you could show a sample of usage to make it more clear!

[jhonfir]

Excellent
I was looking for the same, Thanks a

[CommonSense-ddcc]

This is great. Thank you so much! I'd love if you could make a video on making a fiscal yr DYNAMIC date table that goes back full X number of years + YTD (ex. full 4 fiscal yrs + YTD where fiscal year ends Sep 30th).

[KrishnaKumar-znkg]

What do I do with the created list? After going through such a lengthy process. Could have done it in a jiffy in Excel with Sequence!

[kkaurkify]

First: Don't enter . If you write datestartof then editor helps and you can choose Date.StartOfYear and the duplicated DateDate on start dioesn't appear.
Second: Correct date table includes full years, then time intelligence formulas work. In demo case it must be 1/1/2021 until 13/31/2024.
And it would be nice if it were also said that after importing the table, the table should be marked as a date table and the date column should not be specified. Also needed for correct time intelligence.

[angelaw.dimmick]

I appreciate this method of creating a date table. I never found a convert to table on my Ribbon. I went to properties on the list of apps and manually entered the convert to table and then after that everything was great. Was I missing something?

[luigisir]

Hi, thank you for this video which certainly has interesting content, but the formulas you apply are blurry and for me they are too small due to my vision problem, I can't see them well. It would have been better if she had zoomed in on them. Thank you anyway.

[eslamfahmy]

Thank you for that tutorial, but at the end date col, we have 6 as hard coded, and I need to be a dynamic. Could you support.

[krcarlosm]

Hi, thanks for your video. It will help me a lot. But Im applied a similar approach, and Im having problems to make Power BI recognize my column as Date Type...

I mean, that small icon on Power BI, that indicate the column is a "Date/Time or Date" format, is not appearing. And I'm shure to enter the Power Query steps, and checked every "Type Change", but once I Close and Apply the modifications, all my date columns remains the same (without that calendar icon).

Do anyone could help me with that?

[muhammetmuslu]

A different approach. I liked it. thanks.

[JannievanderWalt]

Awesome explanation 🎉🎉🎉

I have a question is there a way to dynamically determine the first or oldest date in your model. Assuming you have multiple tables with their respective date/time columns.

Thx 🙏