如何强制PowerQuery函数避免重复发起API POST请求？

我们在优化一款对接客户专有SaaS的PoC性能，该SaaS通过含大量CTE的SQL视图准备数据，直接查询全量性能极差。我们通过辅助表+POST请求实现参数化过滤，但API仅支持在视图外加WHERE子句（会先跑全量再过滤），所以采用以下流程：

• 向辅助表发起POST提交过滤参数
• 轮询任务状态直到完成
• 从视图获取过滤后的数据

现在针对多实体（比如4家公司）批量执行上述流程时，SaaS端显示POST请求触发了7-12次（而非预期的4次），次数无规律。推测原因：

• PowerQuery会重复评估POST函数，而非每个实体仅执行一次
• 此前为解决PQ缓存问题给POST函数加了随机数参数，可能加剧了重复执行

核心需求：强制PQ每个实体周期仅执行一次POST请求

现有代码模块 #

顶层调用函数 #

let
    Source = (datasource as text) => let
        RunSequentially = (datasource as text, entities as list, results as list) =>
            if List.IsEmpty(entities) then
                results
            else
                let
                    currentEntity = List.First(entities),
                    restEntities = List.Skip(entities, 1),
                    currentResult = Function.InvokeAfter(() => fnGetAfterPostWithPolling(datasource, currentEntity), #duration(0,0,0,3)),
                    newResults = List.Combine({results, {currentResult}})
                in
                    @RunSequentially(datasource, restEntities, newResults),

        resultTables = RunSequentially(datasource, listEntitiesToQuery, {}),
        combinedTables = Table.Combine(resultTables)
    in
        combinedTables
in
    Source

带轮询的POST+GET函数（fnGetAfterPostWithPolling） #

let fnGetAfterPostWithPolling = (datasource as text, entity as text) =>
    let
        // Run POST and poll until completed
        random = Number.Random(),
        postResult = 
            let dataprocessingId = fnPostToHelper(entity, datasource ,random)
            in dataprocessingId,
        // check status with polling until COMPLETED, returns: {status, numberOfPollingTries}
        dataprocessingStatus = 
            let 
                status = fnPollUntilCompleted(postResult, 0){0}
            in status,

        getResult = 
            if dataprocessingStatus = "COMPLETED"
                then 
                    fnGetData("Datasource", datasource)
                else 
                    error "Unepected status: " & dataprocessingStatus
    in getResult
in fnGetAfterPostWithPolling

轮询状态函数（fnPollUntilCompleted） #

let
    PollUntilCompleted = (dataprocessingId as text, numberIterations as number) =>
        let
            numberIterations = numberIterations + 1,
            status = {
                fnPollStatus(dataprocessingId)
                , numberIterations},
            result = if status{0} = "COMPLETED"
                     then status
                     else @PollUntilCompleted(dataprocessingId, numberIterations)
        in
            result
in
    PollUntilCompleted

单次状态检查函数（fnPollStatus） #

let
    Source = (dataprocessingId as text) => let
        url = "--url--" & dataprocessingId,
        username = "--username--",
        password = "--password--",
        auth_type = "Basic",
        credentials = Binary.ToText(Text.ToBinary(username & ":" & password), BinaryEncoding.Base64),
        headers = [
            #"Authorization" = auth_type & " " & credentials,
            #"Cache-Control"="no-cache, no-store, must-revalidate",
            #"random_number"=Text.From(Number.Random())
        ],
        response = Web.Contents(url, [IsRetry=true, Headers=headers, ManualStatusHandling={400}]
            ),
        // retry if http code 400 (in this case we were to fast and the server could not yet give a the response we need
        responseCode = Value.Metadata(response)[Response.Status],
        result = if responseCode = 400 then @fnPollStatus(dataprocessingId) else
        let getStatus = 
            () =>
            let
                json = Json.Document(response),
                // get status as text
                status = json[status]
            in status
        in getStatus()
    in
        result
in
    Source

数据获取函数（fnGetData） #

let
    Source = (datasourceType as text, datasourceCode as text) => let
        url = "--url--"& datasourceType & "_" & datasourceCode,
        username = "--username--",
        password = "--password--",
        auth_type = "Basic",
        credentials = Binary.ToText(Text.ToBinary(username & ":" & password), BinaryEncoding.Base64),
        headers = [
            #"Authorization" = auth_type & " " & credentials,
            #"Cache-Control"="no-cache, no-store, must-revalidate",
            #"random_number"=Text.From(Number.Random())
        ],
        response = Web.Contents(url, [IsRetry=true, Headers=headers]
            ),
        json = Json.Document(response),
        value = json[value],
        resultTable = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        columnNames = Record.FieldNames(resultTable{0}[Column1]),
        resultTableExpanded = Table.ExpandRecordColumn(resultTable, "Column1", columnNames)
    in
        resultTableExpanded
in
    Source

辅助表POST函数（fnPostToHelper） #

let
    fnPostToHelper = (entity as text, datasourceCode as text, randomNumber as number) =>
    let
        url = "--url--",
        username = "--username--",
        entity = entity,
        password = "--password--",
        auth_type = "Basic",
        credentials = Binary.ToText(Text.ToBinary(username & ":" & password), BinaryEncoding.Base64),
        body = Text.ToBinary(
            "--query body using the entity and datasource parameters--"
    ),
        headers = [
            #"Authorization" = auth_type & " " & credentials,
            #"Content-Type" = "application/json",
            #"Cache-Control"= "no-cache",
            #"x_random" = Text.From(randomNumber)
        ],
        response = Web.Contents(url, [
            Headers = headers,
            Content = body,
            IsRetry = true
        ]),
        json = Json.Document(response),
        result = json[dataprocessingId]

    in
        result
in fnPostToHelper

解决方案 #

1. 移除随机数参数，改用PQ原生缓存控制

随机数参数会让PQ认为每次调用都是新请求，触发重复评估。改用CacheDuration参数强制禁用缓存：

• 移除fnPostToHelper中的randomNumber参数及相关header
• 给Web.Contents添加CacheDuration = #duration(0,0,0,0)

修改后的fnPostToHelper：

let
    fnPostToHelper = (entity as text, datasourceCode as text) =>
    let
        url = "--url--",
        username = "--username--",
        entity = entity,
        password = "--password--",
        auth_type = "Basic",
        credentials = Binary.ToText(Text.ToBinary(username & ":" & password), BinaryEncoding.Base64),
        body = Text.ToBinary(
            "--query body using the entity and datasource parameters--"
    ),
        headers = [
            #"Authorization" = auth_type & " " & credentials,
            #"Content-Type" = "application/json"
        ],
        response = Web.Contents(url, [
            Headers = headers,
            Content = body,
            IsRetry = false, // POST请求禁用自动重试
            CacheDuration = #duration(0,0,0,0) // 强制不缓存
        ]),
        json = Json.Document(response),
        result = json[dataprocessingId]

    in
        result
in fnPostToHelper

同步修改fnGetAfterPostWithPolling，移除随机数生成逻辑：

let fnGetAfterPostWithPolling = (datasource as text, entity as text) =>
    let
        postResult = fnPostToHelper(entity, datasource),
        dataprocessingStatus = fnPollUntilCompleted(postResult, 0){0},
        getResult = 
            if dataprocessingStatus = "COMPLETED"
                then fnGetData("Datasource", datasource)
                else error "Unepected status: " & dataprocessingStatus
    in getResult
in fnGetAfterPostWithPolling

2. 提前批量执行POST并固化结果

先批量完成所有POST请求，将结果和实体绑定后再依次轮询，避免PQ重复评估：

let
    Source = (datasource as text) => let
        // 批量执行所有POST，固化结果
        postResults = List.Transform(listEntitiesToQuery, (entity) => fnPostToHelper(entity, datasource)),
        // 绑定实体与对应的processingId
        entityWithIds = List.Zip({listEntitiesToQuery, postResults}),
        // 依次轮询并获取数据
        RunSequentially = (entityIdPairs as list, results as list) =>
            if List.IsEmpty(entityIdPairs) then
                results
            else
                let
                    currentPair = List.First(entityIdPairs),
                    currentProcessingId = currentPair{1},
                    restPairs = List.Skip(entityIdPairs, 1),
                    dataprocessingStatus = fnPollUntilCompleted(currentProcessingId, 0){0},
                    currentResult = if dataprocessingStatus = "COMPLETED" then fnGetData("Datasource", datasource) else error "Unexpected status: " & dataprocessingStatus,
                    newResults = List.Combine({results, {currentResult}})
                in
                    @RunSequentially(restPairs, newResults),
        resultTables = RunSequentially(entityWithIds, {}),
        combinedTables = Table.Combine(resultTables)
    in
        combinedTables
in
    Source

3. 关闭后台数据预览刷新

PQ自动预览会触发重复评估，可通过以下操作关闭：

• 进入Power Query编辑器 → 文件 → 选项和设置 → 选项 → 数据加载，取消勾选允许后台数据预览

4. 严格控制重试逻辑

IsRetry=true会让PQ自动重试请求，仅在轮询状态的场景保留该参数（比如fnPollStatus中的400错误），POST请求直接设置IsRetry=false，避免误重试。

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