Loading public holidays in Power BI

19 December 2019 Nico-Jacobs Power BI

I recently discovered a REST service which provides the public holidays per country per year. You can find it at https://date.nager.at/swagger/index.html

If you want to use this in Power BI you can call it with the Power Query code below (assuming you have a Year and CountryCode parameter).

let
   Source = Json.Document(Web.Contents("https://date.nager.at/api/v2/PublicHolidays/" & Year & "/" & CountryCode)),
   #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"date", "localName", "name", "countryCode", "fixed", "global", "counties", "launchYear", "type"}, {"date", "localName", "name", "countryCode", "fixed", "global", "counties", "launchYear", "type"}),
   #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"date", type date}})
in
   #"Changed Type"

Or else you can download it as a Power BI template from https://sqlwaldorf.blob.core.windows.net/publicshare/PowerBI/vacation%20days.pbit

Tags :