Extended Date Table (Power Query M function)

[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

[BrianJ]

@dguedes,

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

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, @Melissa has done two videos in the time intelligence series that address creating dynamic start and end dates. You can find them here:

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…

• Brian

[sam.mckay]

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

[Wally]

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

[Melissa]

Hi @Wally,

Welcome to the Forum!

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

.

@Wally

As I am not working now, so I am using the “Adventure Works” MS Access database to build my models.

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.

[Wally]

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

[Melissa]

Hi @Wally,

Great to hear and thanks for the feedback

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

Power BI Builds 5 - Optical Data

Hi All Welcome to the fifth instalment of the Enterprise DNA challenge. The first four challenges were beyond expectation and we want to enhance and build on the start we have made to date. To learn more about the challenge and how it works be sure to check out the forum below! https://forum.enterprisedna.co/t/about-the-power-bi-challenges-category/7619/2?u=haroonali1000 The challenge was created to help embed and improve contestants’ power bi skills. We encourage beginners and newbies to p…

[Wally]

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

[AdamJZ]

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

[Melissa]

Hi @AdamJZ,

Welcome to the forum!

.

@AdamJZ

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.

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.

[AdamJZ]

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)

[Melissa]

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.

Power Query Errors: Please Rebuild This Data Combination

I hope this is helpful.

[AdamJZ]

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

[Melissa]

Hi @AdamJZ,

@AdamJZ

Could we set up holiday table years/dates range dynamically and then use it as optional table in the function that creates date table?

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

@AdamJZ

(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”?)

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.

[Keith]

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

[BrianJ]

@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:

Holidays worldwide

• Brian

[Keith]

@BrianJ,

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

thanks for the information.
Keith

[AdamJZ]

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

[Melissa]

Glad we could help.

[Greg]

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