Power Query调用date.nager.at API获取全球节假日失败求助

安全阿岳

2026-7-19

修复后的Power Query代码及问题说明

问题定位

你的代码主要存在两个核心问题：

API URL拼接错误：在Table.AddColumn的each上下文里，需用_[CountryCode]引用当前行的国家代码，原代码直接写[CountryCode]无法正确取值
缺少错误处理：部分国家可能无节假日数据或请求失败，会导致整个查询中断

修复后的代码

let
    Source = Json.Document(Web.Contents("https://date.nager.at/api/v3/AvailableCountries")),
    CountryCodesTable = Table.FromList(Source, Splitter.SplitByNothing(), {"CountryCode"}),
    SetColumnType = Table.TransformColumnTypes(CountryCodesTable, {{"CountryCode", type text}}),
    // 添加错误处理，避免单个国家请求失败导致全局中断
    AddHolidays = Table.AddColumn(SetColumnType, "Holidays", each 
        try Json.Document(Web.Contents("https://date.nager.at/api/v3/NextPublicHolidays/" & _[CountryCode])) 
        otherwise null),
    // 过滤无有效数据的行
    RemoveNullHolidays = Table.SelectRows(AddHolidays, each [Holidays] <> null),
    ExpandHolidaysList = Table.ExpandListColumn(RemoveNullHolidays, "Holidays"),
    ExpandHolidaysDetails = Table.ExpandRecordColumn(ExpandHolidaysList, "Holidays", 
        {"date", "localName", "name", "countryCode"}, 
        {"HolidayDate", "LocalName", "Name", "CountryCode_FromAPI"}),
    // 统一字段类型，兼容可能的空值
    FinalTypeTransform = Table.TransformColumnTypes(ExpandHolidaysDetails, {
        {"CountryCode", type text},
        {"HolidayDate", type date},
        {"LocalName", type text},
        {"Name", type text},
        {"CountryCode_FromAPI", type text}
    })
in
    FinalTypeTransform