Use Power Query to Create a Dynamic Calendar Table

When you're creating relationships between tables that have dates, it often useful to have a separate calendar table that references all the dates for the year or years that you need. It'll also be nice if it has extra fields or columns that give just the year, month or day name. You can often do this manually in Excel with a few functions, but if you wanted to make it auto-magically update based on your given start and end date, Power Query makes it much easier to do once you set it up.

📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!

#excel
#msexcel
#doughexcel

-~-~~-~~~-~~-~-
Please watch: "Convert Table in a PDF File to Excel"
-~-~~-~~~-~~-~-

[wayneedmondson]

Hi Doug. Great video. Super helpful Power Query dynamic calendar table tips and technique. Thanks for sharing it! Thumbs up!!

[poiboioni]

Hell Doug, Thank you so much for this as I use it frequently.

If people are wondering how to do this without making connections you can try this:

1. Start a blank query
2. = {Number.From(Date.From(Date.AddYears(DateTime.LocalNow(), -2)))..Number.From(Date.From(DateTime.LocalNow()))}
3. The AddYears can be interchanged depending on the date ranges you want from today's date.

Again, I appreciated Doug because, without this video, I wouldn't have figured this out.

[anillpatel]

Excellent video presentation. Very useful. Thanks.

[rvin]

as always..excellent tutorial.keep it up!

[tahm]

Very Useful .. Can this be downlodable ?

[samjovefer]

What if I only want work days to be displayed?