Dynamic Public Holidays Table (Power Query / Power BI)

[ftm]

Hello everyone,

With future reuse in mind, I’m sharing a dynamic Power Query (M) solution that retrieves Greek public holidays (country can be changed) from the trusted Nager.Date public API.

In a recent Power BI project, I was thinking about how holiday tables are usually handled in BI models.

Static tables (for example, 2020–2030) can work, but they raise questions around:

• Who maintains them over time

• How much historical data is really needed (storage & performance)

• How far into the future we should go for planning and predictability

Key characteristics:

• Dynamically calculated based on the current date at refresh time

• 15 years of history, intentionally limited to minimise storage

• 10 years into the future, to support planning and forecasting

• Easily extensible to other countries by changing the country code

The goal is to offer a resilient, low-maintenance approach that can be reused across projects and teams, rather than maintaining static holiday tables.

Full Power Query script and documentation:
👉 GitHub: https://github.com/FotMac/powerquery-public-holidays-gr

Happy to hear feedback, suggestions, or alternative approaches from the community.

[lbendlin · Accepted solution]

Dates are immutable. There is absolutely no need to do this in Power Query or DAX.  Use an externally maintained reference table.

[ralf_anton]

Leider kann ich diese Meinung nicht teilen. Eine statische Referenztabelle muss regelmäßig von Hand angepasst werden. Da frage ich mich schon, wozu dann überhaupt EDV? Ich finde den geposteten Link, über den man unter anderem auf diesen Link oder diesen Link kommt, absolut hilfreich! @ftm, ich danke Dir dafür!

Zumal man gerade bei internationalen Feiertagen erst ewig recherchieren müsste.

Hier kann die Demo für die schnelle Recherche unter Nutzung Deines Links heruntergeladen werden.

Datei herunterladen, in Spalte A ein beliebiges Land auswählen, oder in Zelle I4 das Jahr ändern, dann aktualisiert sich die rechte Feiertagstabelle entsprechend.

Hinweis: Datei enthält Makros zur Aktualisierung der Feiertage. Wer die Makros deaktiviert muss die rechte Tabelle manuell aktualisieren.

[ftm]

Good points. I absolutely agree that a static reference table is the most appropriate approach.

The idea here was mainly to demonstrate a reusable Power Query pattern that could potentially be adapted for larger or more frequently changing datasets.

Out of curiosity, in your experience, what kinds of datasets do you think this type of approach would be most appropriate for?

Appreciate the feedback, @ToddChitt , @lbendlin !

[ToddChitt]

@ftm What is the problem you are trying to solve here? Is a 'dynamic solution' that has zero maintenance overhead worth the effort as opposed to a simple solution with minimal maintenance overhead.

Is the juice worth the squeeze?

[v-pgoloju]

Hi @ftm,

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @ftm,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @lbendlin and @ToddChitt  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[ToddChitt · Accepted solution]

Totally agree with @lbendlin . 30 years of dates is less than 11,000 records. 30 years of holidays, (at least ones you need to track, like TIME OFF days) would be in the hundreds. 

Stick it in an external table and move on.

(I have seen code that that was so designed as to allow additional MONTHS to be added to a calendar. JUST IN CASE!)

[lbendlin · Accepted solution]

Dates are immutable. There is absolutely no need to do this in Power Query or DAX.  Use an externally maintained reference table.