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.

[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.

[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!)

[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

[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