Extended Date Table (Power Query M function)

@dguedes,

Here’s a post that covers that process in detail:

In addition, @Melissa has done two videos in the time intelligence series that address creating dynamic start and end dates. You can find them here:

  • Brian
2 Likes

This really is the best date table around the web.

Every Power BI user should be using this as there core date table.

Awesome work Melissa

Sam

7 Likes

Hi Melissa

Thanks for this video — it is very good—in fact, excellent. Unfortunately, I have discovered an issue with the “Fiscal Year” Calculation.

As I am not working now, so I am using the “Adventure Works” MS Access database to build my models. As this database begins in 2001, this causes a problem when calculating the Fiscal Year. The Extended Date Table returns FY02 and FY2 as when the +1 is added if less than the FYStartMonth, it is returning the 2 without the 0 in front. If I click on FY02 and FY2 together, I get the correct results. Obviously, this only happens thru 2009 — from 2010 forward, everything works great.

I am not good at M, but I believe I did find the problem but not sure how to resolve but I will keep working on it. An M expert should be able to resolve quickly. I could solve it in excel :>) — or I will remap the data to post-2010 dates.

How many users need FY back in 2001-2009? Not many but…

Again, thanks for your contribution and you have been a great addition to Sam’s team!
Thanks
Wally

2 Likes

Hi @Wally,

Welcome to the Forum!

Must admit I new that could be an issue but forgot to fix it :innocent: It’s sorted now and I also updated the FY Offset. With some community development this is turning into a great Date table :+1:

.

Watch out for the eDNA Challenges, it’s a great way to practice with different types of data, learn and pick up new techniques others used. I hope to see some of your work there.

4 Likes

Hi @Melissa

Your update worked great ---- very fast. The “Text.PadEnd” code worked great. I will not forget that one!

I hope to join the eDNA Challenge, but while I can build some great reports, the techniques I have seen you and others use are outstanding and I need to up my game in that area. I will get there!

Thanks again for your outstanding work
@Wally

1 Like

Hi @Wally,

Great to hear and thanks for the feedback :+1:

If you have the time don’t wait to join the challenges and just jump in. There’s so much to learn from them whether you’re a beginner or a seasoned analyst… you’ll find this is a great community giving lots of feedback, tips, tricks and all sorts of support.

Here’s a link to the challenge, released yesterday. I hope you’ll reconsider and I look forward to seeing your entry :smiley:

1 Like

Hi @Melissa

Funny you should write that as I was just looking for the latest challenge. I will use it to focus my training. Do not expect much from me at the beginning, but…

Thanks for the push!
@Wally

3 Likes

Dear Forum Members,
At first I would like to thank @Melissa for the excellent M script.
I’ve immediately given it a try. It helped me very much.
The data created by that script refreshes without a problem in PB Desktop.
But the troubles have come when I’ve published my report with date table into PB Service.
The refresh there gave me an error:

[Unable to combine data] Section1/Holidays/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

That has to do something with the table of holidays which I read from the Internet and use as an option in the M script.

Could you please give me a piece of advice what to do with this error message.

Thanks in advance
Adam

2 Likes

Hi @AdamJZ,

Welcome to the forum!

.

Can you share a PBIX file with the Date- and Holiday table queries you’ve set up, also include the date range you want to cover so I can take a look?

Thanks.

2 Likes

Hello @Melissa,
Trying to prepare data for you I’ve discovered that the problem is located in my holidays table.
The holidays have to be listed for all the years from sales table.
I’ve prepared the file using your sales data from Ultimate Beginners Guide to DAX course (it’s just an example).
Example.pbix (412.7 KB)

1 Like

Hi @AdamJZ,

After taking another look at your error the answer is that you cannot combine an external data source with another query. See this article for more details and how to resolve it.

I hope this is helpful.

1 Like

Hello @Melissa,
I’ve read the article you suggested and got the idea. Thanks.
But I still have a questions.
I watched Enterprise DNA videos on date table, on setting start and end dates and on creating a holiday table. Could we set up holiday table years/dates range dynamically and then use it as optional table in the function that creates date table? (Your colleague Brian got the holiday dates from the Internet but finally created Holiday table in Excel. Does that mean that holiday table used in date table function ought to be “static”?)
Regards
@AdamJZ

1 Like

Hi @AdamJZ,

Yes but you’d have to avoid that combination of external data source with another query.
.

Not exactly, for example I created a huge Holiday table in excel and stored that on SharePoint. Now I dynamically get Holidays from that SharePoint file without having to rely on an internet site remaining active, keeping the same data structure and so on. It also avoids that external data source issue.

I hope this is helpful.

1 Like

Hi Everyone,

I would like to bring up an issue that every country has different holidays and different dates for each holiday.

Does anyone have that information for different countries?

thanks
Keith

1 Like

@Keith,

There are lots of sites on the web with this information, but one that I particularly like is this one which has all countries’ holidays listed through 2030. It is structured very similarly to the site I used in my video, so the same web scraping techniques should work there too:

  • Brian
4 Likes

@BrianJ,

I just thought it might be part of the m code.

thanks for the information.
Keith

1 Like

Thanks @Melissa for your suggestions.
@BrianJ thank you for the great resource.
We could mark the topic as solved.
Regards,
@AdamJZ

1 Like

Glad we could help. :+1:

1 Like

Hi @Melissa.

What a great date table you’ve posted and kept up-to-date for us … thank you! It’s the only date table I use!

While preparing another solution, I needed [Day Type] so added [IsWeekday], [IsWeekend], and [Day Type] (and [IsBusinessDay] while I was at it). Here’s the (very rough) M code fragment I came up with:

InsertIsWeekday = Table.AddColumn(InsertChangedType, "IsWeekday", each 
  if Date.DayOfWeek([Date]) = Day.Saturday then false 
  else if Date.DayOfWeek([Date]) = Day.Sunday then false 
  else true, type logical),
InsertIsWeekend = Table.AddColumn(InsertIsWeekday, "IsWeekend", each 
  if [IsWeekday] = false then true else false, type logical),
InsertDayType = Table.AddColumn(InsertIsWeekend, "Day Type", each
  if [IsHoliday] = true then "Holiday"
  else if [IsWeekend] = true then "Weekend"
  else if [IsWeekday] = true then "Weekday"
  else null, type text),
InsertIsBusinessDay = Table.AddColumn(InsertDayType, "IsBusinessDay", each 
  if [IsWeekday] = true then
   if [IsHoliday] = false then true 
    else false 
  else false, type logical)

I’m still quite the M code newbie, so I’m sure there are better/more efficient ways to write this, but thought I’d add it to the thread in case it was useful to anyone.

Greg

3 Likes

@Greg,

While this sort of effort is never wasted, I think everything except the text DayType can already be done off the existing IsWorkingDay field.

  • Brian
1 Like