Powerquery get holiday list from web API

Question

I am trying colect data from API https://date.nager.at/ task is collect holydays for all avilable countries but my query nor works. Can anybody help me ?

let
    Source = Json.Document(Web.Contents("https://date.nager.at/api/v3/AvailableCountries")),
    CountryCodes = Source,
    CountryCodesTable = Table.FromList(CountryCodes, Splitter.SplitByNothing(), {"CountryCode"}),
    RenamedColumns = Table.TransformColumnTypes(CountryCodesTable, {{"CountryCode", type text}}),
    Holidays = Table.AddColumn(RenamedColumns, "Holidays", each Json.Document(Web.Contents("https://date.nager.at/api/v3/NextPublicHolidays/" & [CountryCode]))),
    ExpandHolidays = Table.ExpandListColumn(Holidays, "Holidays"),
    ExpandHolidaysRecord = Table.ExpandRecordColumn(ExpandHolidays, "Holidays", {"date", "localName", "name"}, {"HolidayDate", "LocalName", "Name"}),
    RenamedColumnsAgain = Table.RenameColumns(ExpandHolidaysRecord,{{"CountryCode", "CountryCode"}}),
    ChangeTypes = Table.TransformColumnTypes(RenamedColumnsAgain,{{"CountryCode", type text}, {"HolidayDate", type date}, {"LocalName", type text}, {"Name", type text}})
in
    ChangeTypes

Answer 1

Try

let Source = Json.Document(Web.Contents("https://date.nager.at/api/v3/AvailableCountries")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error) ,
ExpandList= List.Distinct(List.Combine(List.Transform(Table.Column( #"Converted to Table", "Column1"), each if _ is record then Record.FieldNames(_) else {}))),
Expand= Table.ExpandRecordColumn( #"Converted to Table", "Column1", ExpandList,ExpandList),
#"Added Custom" = Table.AddColumn(Expand, "Custom", each Json.Document(Web.Contents("https://date.nager.at/api/v3/NextPublicHolidays/" & [countryCode]))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"name", "name2"}, {"countryCode", "countryCode2"}}),
ExpandList2= List.Distinct(List.Combine(List.Transform(Table.Column( #"Renamed Columns", "Custom"), each if _ is record then Record.FieldNames(_) else {}))),
Expand2= Table.ExpandRecordColumn( #"Renamed Columns", "Custom", ExpandList2,ExpandList2),
/// you can stop here if you dont need to expand counties or types
ChangeList1=Table.TransformColumns(Expand2, {{"counties", each if Value.Is(_, type list) then _ else {_} }} ),
#"Expanded counties" = Table.ExpandListColumn(ChangeList1, "counties"),
ChangeList2=Table.TransformColumns(#"Expanded counties", {{"types", each if Value.Is(_, type list) then _ else {_} }} ),
#"Expanded types" = Table.ExpandListColumn(ChangeList2, "types")
in  #"Expanded types"