PowerQuery中基于GraphQL结果动态更新参数实现多API调用

现有代码通过固定步长调用GraphQL API实现分页，需改为动态获取前一次响应中最大的tax_assessor_id作为下一次调用的参数，以下是修改后的完整代码：

let
    Source = List.Generate(
        // 初始状态：首次API调用的参数及返回结果
        () => [p_taxid = 100000, tadata = fnGetTA(100000)],
        // 终止条件：ID未达上限且API返回有效数据
        each [p_taxid] <= 400000 and List.Count([tadata][tax_assessor]) > 0,
        // 迭代逻辑：从上次结果提取最大tax_assessor_id，作为新参数调用API
        each 
        let
            // 提取上次返回的所有tax_assessor_id
            idList = List.Transform([tadata][tax_assessor], (record) => record[tax_assessor_id]),
            // 获取最大ID（空列表时返回null避免报错）
            maxId = if List.Count(idList) > 0 then List.Max(idList) else null,
            // 调用API传入新参数
            newData = fnGetTA(maxId)
        in
            [p_taxid = maxId, tadata = newData]
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"p_taxid", "tadata"}, {"p_taxid", "tadata"}),
    #"Expanded tadata" = Table.ExpandRecordColumn(#"Expanded Column1", "tadata", {"tax_assessor"}, {"tax_assessor"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded tadata", {"p_taxid"}, "Attribute", "Value"),
    #"Expanded Value" = Table.ExpandListColumn(#"Unpivoted Columns", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"tax_assessor_id", "address", "city", "state", "zip", "latitude", "longitude", "year_built"}, {"tax_assessor_id", "address", "city", "state", "zip", "latitude", "longitude", "year_built"})
in
    #"Expanded Value1"

关键修改说明 #

1. 迭代逻辑替换：

   • 用List.Transform从上次返回的tax_assessor记录列表中提取所有tax_assessor_id，生成ID列表
   • 用List.Max获取列表中的最大ID，加入空列表判断避免运行报错
   • 将最大ID作为参数传入fnGetTA，生成新的API响应数据

2. 终止条件优化：

   • 保留原有的ID上限判断[p_taxid] <=400000，新增List.Count([tadata][tax_assessor]) > 0判断，当API返回空结果时自动终止循环，防止无限调用

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