Extended Date Table (Power Query M function)

[Melissa]

Just adding some other search terms: power query calendar table, calendar function

[Whitewater100]

Hi Melissa:

This really cool and thoughtful of you for sharing!

Best regards,

Bill S

[NajahS]

Thanks @Melissa, you are awesome
Have a beautiful day
Najah

[sam.mckay]

Agree just amazing work.

[DavidNealon]

Any chance we can get a separate Extended Time Table seeing we are suggested to split out time from date. Would be great if we could get an Extended Time Table to go with it.

[Melissa]

Hi David,

Makes sense, you can find it here:

Time Table (Power Query M function)

Hi everyone, @DavidNealon thought it would be a nice addition if there was a Time Table Power Query M code function available in this category as well. Now I can’t take any credit because it was designed by Gina Meronek, you can find it and the full article she wrote here. When invoked the function creates a Time Table at a granularity of Minutes. If your requirement is different that can easily be fixed. Note that other changes might be required as well but to give you some ideas: For Hourl…

[Walter]

Thank you again Melissa!

[pedroccamara]

Very good Melissa. I’ve just voted for a new virtual pbi training about all this. I really hope you guys manage to get some time to do it.
Thank you so much

[Melissa]

Hi Pedro,

As a matter of fact @BrianJ and I are collaborating on a Time Intelligence series as we speak and of course we will also be covering the Date table. So if you haven’t already, subscribe to the Enterprise DNA YouTube channel so you won’t miss a thing!

[pedroccamara]

oooohhhh that is soooooooo cooooll!!! and yes i am a dna youtube subscriber for quite a while now. And it’s a great idea to teach also about TI measures concerning last week or weeks or month or quarter regarding the beginning of the year.
Thanks a lot Melissa. That is great news!!!

[delaunoy]

Muchas gracias Melisa, Awesome

[Mohammed_ali]

This is very helpful Melisa, you are a rockstar here.

[kjssdca]

@Melissa I need to adjust the ISO Year column to match our “week-years.”
Our weeks run Sunday - Saturday, and our billing “months” end on the last Saturday in the calendar month.
I have been trying to adjust the code in the InsertWeekNumber= line to match this calendar. Can you explain out the last ‘else’ line in that code?
else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Sunday)+1)+10)/7), type number),
Specifically - Why is it adding 1, then 10 before dividing by 7?
Learning a ton here. . .
Thank you.

[Melissa]

Hi @kjssdca,

Thanks for your question.

By default the Power Query Date.DayOfWeek function returns a number between 0 and 6 starting on Sunday. To return the weekday number according to ISO8601 rules, you have to add the optional first-weekday value (= Day.Monday) and change the numbering so it runs between 1 and 7.
This explains the +1

The +10 / 7 is part of the algorithm for calculating the week number, you can find more on that here.

If you need assistance creating a customized week number, please create a new topic. You’ll find that there’s an increasing number of active members here who’d like to help out.

[Hesham]

Thank you @Melissa for sharing! Date Table is getting better and better

Your code has opened my appetite to keep the “EndDate” dynamic, so it will always be the last day of the current year. That would save me from updating/maintaining the Dates table in all my reports next year(unless there is another way which I am not aware of )

Using your M code, I have:
1- Removed “EndDate as date” from the first line and;
2- Added another line “EndDate = Date.EndOfYear(CurrentDate),” after the “Current Date”.

[Melissa]

Hi @Hesham,

Within this category you can also find a topic on creating a dynamic Start- and Enddate.

Creating a dynamic Start-/EndDate for the Date table

Hi everyone, To make it easier to find here are some Power Query techniques to make the Date table Startdate and/or Enddate dynamic. If you want to give that a try, go to Power Query, select New Source, Blank Query, open the Advanced Editor, paste in this Date table M code and Invoke that function. A new query will appear in the Queries pane with the name ‘Invoked Function’ in the formula bar of that query’s Source step you can distinguish the following elements. Date table function query na…

.
In addition there will also be a video on that topic in the Time Intelligence series Brian and I are doing. We’ll add a link here as soon as it’s released to the Enterprise DNA Channel.

Time Intelligence series by BrianJ and Melissa

Hi everyone, We’ve brought the Time Intelligence series we did for Enterprise DNA TV together in this post so it’ll be easier to find and navigate through completely. @BrianJ and I had great fun doing these. If you have any questions or concerns about the content covered in these video’s please create a new topic, refer to the video and @mention BrianJ or Melissa. But we’d also welcome your feedback within this thread of course. – THANK YOU – How to create a Holiday table [Ho…

.
Thanks for sharing your method

[Yeriel]

Thanks @Melissa!!!

This post came just right in time!!

[Paul]

Hi Melissa,
The optional parameters work fine, would it be possible to add a selection parameter to show Monday as Weekday 1 please, so users can select either 0 or 1.
Paul

[Melissa]

Hi Paul,

Thanks for your question!

That sounds like a nice addition indeed. So added the optional WDStartNum parameter this allows you to set the [DayOfWeek] numbering from either 0-6 or 1-7. However if omitted, the standard numbering will be 0-6.

Keep in mind that this doesn’t affect the start of the week day, that will always remain Monday as is custom to this Calendar and the ISO week logic.

[dguedes]

Hi Melissa,

that’s very cool. Thank you for that.
I’d like to ask you a favour, what if the StartDate and EndDate I wanted to grab from the Table “Sales”, column “Sales Date”, using MIN and MAX?

Can you please help me with that?

Thanks.
Daniel