Forum Discussion
Dynamic Public Holidays Table (Power Query / Power BI)
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.
Dates are immutable. There is absolutely no need to do this in Power Query or DAX. Use an externally maintained reference table.
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!)
7 Replies
Dates are immutable. There is absolutely no need to do this in Power Query or DAX. Use an externally maintained reference table.
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!)
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 KumarHi @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 KumarGood 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?
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.
