Power Query批量调用ECB API卡顿超时问题求助

问题背景 #

使用Power Query从欧洲央行（ECB）API批量下载1000个CSV数据，API请求URL存储在名为api_requests的工作表中：

• 原始代码处理50个请求正常，但100个及以上时无限运行无结果；移除超时设置直接触发超时错误
• Python的pd.read_csv()可在2-3分钟完成全部1000个请求
• 修改后的代码卡在Table.PromoteHeaders步骤无法完成加载

原始代码：

let 
    Source = Excel.CurrentWorkbook(){[Name="api_requests"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Syntax", type text}, {"protocol", type text}, {"wsEntryPoint", type text}, {"resource", type text}, {"flowRef", type text}, {"key", type text}, {"parameters", type text}, {"Full API request", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Full API request"}),
    #"Kept Range of Rows" = Table.Range(#"Removed Other Columns",0,100),
    url_list = #"Kept Range of Rows"[Full API request],
    combined_results = Table.Combine(List.Transform(url_list, each Table.PromoteHeaders(Csv.Document(Web.Contents(_, [Timeout=#duration(0,0,60,0)]),[Delimiter=",", Columns=32, QuoteStyle=QuoteStyle.None]), [PromoteAllScalars=true])))
in combined_results

修改后卡壳的代码：

let Source = Excel.CurrentWorkbook(){[Name="api_requests"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Syntax", type text}, {"protocol", type text}, {"wsEntryPoint", type text}, {"resource", type text}, {"flowRef", type text}, {"key", type text}, {"parameters", type text}, {"Full API request", type text}}),
#"Kept Range of Rows" = Table.FirstN( #"Changed Type" ,1050),
#"Added Custom" = Table.AddColumn(  #"Kept Range of Rows", "Custom", each Csv.Document(Web.Contents([Full API request], [Timeout=#duration(0,0,60,0)]),[Delimiter=",", Columns=32, QuoteStyle=QuoteStyle.None])),
tables_with_headers = Table.AddColumn(#"Added Custom", "step2", each Table.PromoteHeaders([Custom], [PromoteAllScalars=true]))
in  tables_with_headers

解决办法 #

1. 添加请求延迟+分批处理，规避API速率限制 #

ECB API大概率有请求频率限制，Power Query无间隔的批量请求会被限流，导致超时或无限等待。以下代码通过分批+间隔延迟控制请求节奏：

// 定义延迟函数，控制请求间隔
fnDelay = (delaySeconds as number) =>
    let
        Start = DateTime.LocalNow(),
        Wait = List.Generate(() => DateTime.LocalNow(), each DateTime.LocalNow() < DateTime.AddSeconds(Start, delaySeconds), each DateTime.LocalNow()),
        Result = null
    in
        Result;

// 主处理逻辑
let
    Source = Excel.CurrentWorkbook(){[Name="api_requests"]}[Content],
    #"保留URL列" = Table.SelectColumns(Source, {"Full API request"}),
    #"转换URL格式" = Table.TransformColumnTypes(#"保留URL列", {{"Full API request", type text}}),
    // 按每批50个请求拆分，可根据实际情况调整批次大小
    #"拆分批次" = Table.Split(#"转换URL格式", 50),
    // 定义单批次处理逻辑
    处理单批次 = (batch as table) =>
        let
            // 批次开始前延迟2秒，降低整体请求频率
            批次前延迟 = fnDelay(2),
            URL列表 = batch[Full API request],
            // 定义单个URL处理逻辑
            处理单个URL = (url as text) =>
                let
                    // 单个请求间延迟0.5秒，避免触发限流
                    请求延迟 = fnDelay(0.5),
                    // 缩短超时时间至30秒，避免无意义等待
                    下载数据 = Web.Contents(url, [Timeout=#duration(0,0,30,0)]),
                    解析CSV = Csv.Document(下载数据, [Delimiter=",", QuoteStyle=QuoteStyle.None]),
                    // 捕获异常，避免单个请求失败导致整个批次中断
                    提升表头 = try Table.PromoteHeaders(解析CSV, [PromoteAllScalars=true]) otherwise null
                in
                    提升表头,
            // 处理当前批次所有URL
            批次结果 = List.Transform(URL列表, 处理单个URL),
            // 过滤掉失败的请求结果
            过滤有效表格 = List.RemoveNulls(批次结果),
            // 合并当前批次数据
            合并批次数据 = Table.Combine(过滤有效表格)
        in
            合并批次数据,
    // 处理所有批次
    所有批次结果 = List.Transform(#"拆分批次", 处理单批次),
    // 合并所有批次最终结果
    最终合并数据 = Table.Combine(所有批次结果)
in
    最终合并数据

2. 优化表头提升逻辑，避免格式不一致导致卡顿 #

如果部分CSV的表头格式不统一，Table.PromoteHeaders会拖慢整个处理流程。以下代码自动取第一个有效请求的表头作为标准，只合并表头匹配的数据：

// 获取标准表头（自动取第一个URL的表头）
fn获取标准表头 = (url as text) =>
    let
        下载数据 = Web.Contents(url, [Timeout=#duration(0,0,30,0)]),
        解析CSV = Csv.Document(下载数据, [Delimiter=",", QuoteStyle=QuoteStyle.None]),
        标准表头 = if Table.RowCount(解析CSV) > 0 then Table.ColumnNames(Table.PromoteHeaders(解析CSV, [PromoteAllScalars=true])) else {}
    in
        标准表头;

// 主处理逻辑
let
    Source = Excel.CurrentWorkbook(){[Name="api_requests"]}[Content],
    #"保留URL列" = Table.SelectColumns(Source, {"Full API request"}),
    #"转换URL格式" = Table.TransformColumnTypes(#"保留URL列", {{"Full API request", type text}}),
    // 获取第一个URL的标准表头
    第一个URL = #"转换URL格式"{0}[Full API request],
    标准表头 = fn获取标准表头(第一个URL),
    // 定义单个URL处理逻辑
    处理单个URL = (url as text) =>
        let
            请求延迟 = fnDelay(0.3),
            下载数据 = Web.Contents(url, [Timeout=#duration(0,0,30,0)]),
            解析CSV = Csv.Document(下载数据, [Delimiter=",", QuoteStyle=QuoteStyle.None]),
            提升表头 = try Table.PromoteHeaders(解析CSV, [PromoteAllScalars=true]) otherwise null,
            // 验证表头是否匹配标准格式，不匹配则过滤
            验证表头 = if 提升表头 <> null and Table.ColumnNames(提升表头) = 标准表头 then 提升表头 else null
        in
            验证表头,
    // 处理所有URL
    所有结果 = List.Transform(#"转换URL格式"[Full API request], 处理单个URL),
    过滤有效数据 = List.RemoveNulls(所有结果),
    最终合并数据 = Table.Combine(过滤有效数据)
in
    最终合并数据

3. 启用并行加载（谨慎使用） #

Power Query默认串行处理，可通过ConcurrentRequests选项启用并行，但需配合延迟使用，避免触发API限流：

// 在Web.Contents中添加并行参数，示例设置并行5个请求
下载数据 = Web.Contents(url, [Timeout=#duration(0,0,30,0), ConcurrentRequests=5])

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