Create Date Table or Calendar in Power Query M (Complete Guide)

This complete guide teaches you how to create a date table. You will find the easiest way to create a calendar, learn how to add the most important columns and find a trick that saves you a lot of time in creating columns. And the best thing? You only prepare this once and can reuse the same code again and again.

WRITTEN BLOGPOST:

Master Functions and Syntax in M

ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.

SUPPORT MY CHANNEL

SUBSCRIBE TO MY CHANNEL

LET'S CONNECT:

Thank you for your support!

Chapters:
00:00 Introduction
02:11 Create Essential Columns
03:34 Adding Custom Columns
04:53 Custom Formatting with Date.ToText
09:45 Adding Weekend vs Workingdays

MUSIC
Licensed under Creative Commons: By Attribution 3.0 License

#Calendar #PowerQuery #BIGorilla

[igiklrz]

Your voice is so calm and smooth. Way better than some accents. I enjoy listening, Thank you

[rdaleprice]

Great video! It would help to explain how to set up the Date Parameters at the first of the video.

[chakrabmonoj]

Fantastic lesson -much simpler and effective than many other lesson I wasted my time watching. Thank you for posting this!

[maxivy]

Amazing lesson. Also really really helpful that you wrote the code for relative week in power query. You are a Jedi Master

[raitup]

Omg! in your blog you have more information about the topic, and with lot of details. Thanks for sharing your knowledge in Power query / BI

[williamarthur]

Thanks really succinct and understandable, also you seem to be the only person (this refers to website rather than youtube tutorial) who has bothered to explain the #duration syntax, which is straight forward.
ONCE YOU KNOW IT. So thanks again.

[HYHY]

This is everything I needed! Thank you!
For your reference, my default Canadian setting showed day of week as Saturday as 6 and Sunday as 0, so I ended up with something like
= Table.AddColumn(#"Added Custom2", "Is Weekend", each if Date.DayOfWeek([Date]) = 0 or Date.DayOfWeek([Date]) = 6 then "Weekend" else "Weekday")

[suki]

Great tutorial, Rick! Very informative, and useful. Thanks!

[JanBolhuis]

Great video and explanation. Thank you.

[nelson_k_d]

Create date table with parameter:
= List.Dates(StrtDt, Duration.Days (EndDt - StrtDt) + 1, #duration(1, 0, 0, 0))

StrtDt & EndDt have been imported from two tables in excel, dates can be changed in excel without opening the query.
The dates are then drilled to form items, used in the above formula to create a datetable :)

[mesfinzewdu]

Great video, I was looking for how to create a Month start and Month end fiscal column, which the Month ends at the last Saturday of each month and the Month starts at the following Sunday of each month. If possible please create another lesson.

[wasfiathhar]

could you please share how to calculate ISO week number

[mercspalms]

How do I make the start date to be the current month every time I refresh the data? I am working with an open order report and I am trying to pull only the 12 months ahead (opposite of a trailing 12 monthly report).

[txreal]

What if my columns are datetime? I also need to work with time, beside date.
Thanks! follower subscriber

[egordontov]

Thank you for this

Do you need to reference start and end dates parameters from calendar table?

Or isn it picked up automatically?

[victorarayal]

Great video!
Is there a easy way to add columns to show if a day is in the last month, or in the last 6 months or in the last year?

[RonDavidowicz]

Nice, now can you show us what we can do with this calender query?

[iankr]

Thanks. How do create the StartDate and EndDate parameters?

[BrianMatthews-xspl]

Is there and easy way to create a relative week index or offsets to this calendar?

[kcdcusm]

Hi I have created the startdate and enddate query using List.Min and List.Max and table name and column. When I created the calendar tables using these two, it's really long to get to the end. Samething each time I'm creating, the Year, Month, etc columns. Is this normal?