Problem of the Week #12 (PQ) - Perpetual Holiday Calendar

Hi Everyone,

This was definitely a more advanced scenario. But the nice thing about these challenges is that you can always revisit them, so if you haven’t gotten around to it, you can still do so at your own convenience.

I’m not going to dive into my own specific solution because I cover that in the solution video you can find on the enterprise DNA YouTube channel.

The solutions posted to date, have shown a wide range of methods on how to deal with a scenario like this. Generally speaking there where two main solution types, one driven by custom functions and the other driven by merges.

I also need to mention that the task was to create a dynamic perpetual holiday table (based on the Dates table) and not everyone quite met that requirement, but in all cases that will be an easy fix. Nonetheless there are takeaways from every entry so if you struggled with this particular challenge or want to learn more about Power Query/M - I do encourage you to explore all of them on your own.

Let me highlight some things that stood out to me:

  • @AntrikshSharma object-oriented programming approach is a must see for everyone seriously interested in M. Furthermore, he, like @Rajesh and @jbressan used an excel like calculation to determine holiday dates.
  • @datazoe’s solution is the only one that incorporated the full logic back into the Extended Date table M code function query, kudos for that!
  • @jbressan used the function Record.FieldOrDefault , look that up if you are unfamiliar.
  • @quantumudit created a separate merge query with additional keys, that is a great way to break down this problem.
  • Eric (non-member) and @alexbadiu created separate supporting tables for different types holidays calcs and then combined them, also a great way to break down this problem.
  • Let’s stay on the subject of problem break down, as always a must read is @Greg’s write up, who took the multiple merge approach.
  • @BrianJ what a comeback, great effort!

Kudos to all who participated this round! :+1: :+1: :+1:
I hope you guys had fun, I surely did!

Don’t forget if you’ve entered your solution on time and want to be eligable for that cool eDNA Problem Solver laptop sticker make sure to send your details and postal address to this email: