如何从欧洲央行API获取历史汇率数据？Power Query M求助

需求说明 #

• 新手，目前仅能获取单日欧元汇率，需获取**2000年1月1日起（以最早可获取日期为准）**的历史汇率数据
• 期望输出宽表格式（日期为行，指定货币为列）：

当前问题 #

现有Power Query M代码仅能输出单日长格式数据，不符合需求：

当前代码：

let
    url = "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml",

    Source = Xml.Tables(Web.Contents(url)),

    ExpandedSender = Table.ExpandTableColumn(Source, "Sender", {"name"}, {"Sender.name"}),
    ExpandedEurofxref = Table.ExpandTableColumn(ExpandedSender, "http://www.ecb.int/vocabulary/2002-08-01/eurofxref", {"Cube"}, {"Eurofxref.Cube"}),
    ExpandedCube = Table.ExpandTableColumn(ExpandedEurofxref, "Eurofxref.Cube", {"Cube"}, {"Cube.Cube"}),
    ExpandedCubeCube = Table.ExpandTableColumn(ExpandedCube, "Cube.Cube", {"Cube", "Attribute:time"}, {"Cube.Cube.Cube", "Date"}),
    ExpandedRate = Table.ExpandTableColumn(ExpandedCubeCube, "Cube.Cube.Cube", {"Attribute:currency", "Attribute:rate"}, {"Currency", "Rate"}),

    FilteredCurrencies = Table.SelectRows(ExpandedRate, each [Currency] = "USD" or [Currency] = "GBP"),

    ReplacedComma = Table.TransformColumns(FilteredCurrencies, {{"Rate", each Text.Replace(Text.From(_), ".", ","), type text}}),

    USDDate = Table.SelectRows(ReplacedComma, each [Currency] = "USD"){0}[Date],

    EURRow = Table.FromRecords({
        [Currency = "EUR", Rate = "1", Date = USDDate]
    }),

    CombinedTable = Table.Combine({ReplacedComma, EURRow}),
    #"Removed Columns" = Table.RemoveColumns(CombinedTable,{"subject", "Sender.name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Currency", "Currency_name"}})
    
in
    #"Renamed Columns"

解决方案 #

关键调整点 #

1. 替换数据源链接：使用ECB历史汇率XML（包含1999年以来的全量数据），替换原单日数据链接
2. 长表转宽表：通过Table.Pivot将长格式数据转换为需求的宽表结构
3. 简化冗余步骤：移除不必要的EUR行插入逻辑（欧元为基准货币，无需额外添加）
4. 日期范围筛选：添加步骤筛选2000年1月1日之后的数据

修改后的代码 #

let
    // 替换为ECB历史汇率数据源（包含1999年至今的所有数据）
    url = "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml",
    Source = Xml.Tables(Web.Contents(url)),
    
    // 展开XML层级，提取核心字段：日期、货币、汇率
    ExpandedEurofxref = Table.ExpandTableColumn(Source, "http://www.ecb.int/vocabulary/2002-08-01/eurofxref", {"Cube"}, {"Eurofxref.Cube"}),
    ExpandedDateCubes = Table.ExpandTableColumn(ExpandedEurofxref, "Eurofxref.Cube", {"Cube", "Attribute:time"}, {"DailyCubes", "Date"}),
    ExpandedRates = Table.ExpandTableColumn(ExpandedDateCubes, "DailyCubes", {"Attribute:currency", "Attribute:rate"}, {"Currency", "Rate"}),
    
    // 筛选需要的货币类型（USD、GBP）
    FilteredCurrencies = Table.SelectRows(ExpandedRates, each [Currency] = "USD" or [Currency] = "GBP"),
    
    // 将汇率转换为数值类型（适配区域小数点格式）
    ConvertRateToNumber = Table.TransformColumns(FilteredCurrencies, {{"Rate", each Value.From(_), type number}}),
    
    // 透视列，将货币转为表头，日期为行
    PivotedTable = Table.Pivot(ConvertRateToNumber, List.Distinct(ConvertRateToNumber[Currency]), "Currency", "Rate"),
    
    // 重命名列以匹配需求格式
    RenamedColumns = Table.RenameColumns(PivotedTable, {
        {"USD", "美元（USD）"},
        {"GBP", "英镑（GBP）"}
    }),
    
    // 转换日期格式并筛选2000年1月1日之后的数据
    ConvertDateType = Table.TransformColumns(RenamedColumns, {{"Date", each Date.From(_), type date}}),
    FilteredDateRange = Table.SelectRows(ConvertDateType, each [Date] >= #date(2000, 1, 1))
in
    FilteredDateRange

代码说明 #

• 数据源替换为eurofxref-hist.xml，直接获取所有历史汇率数据
• Table.Pivot是实现长表转宽表的核心步骤
• 汇率转为数值类型，支持后续计算分析
• 最后通过日期筛选确保数据起始时间符合要求

内容的提问来源于stack exchange，提问作者love