Power BI调用Autotask API时gatherpagingdata未识别错误求助

错误原因 #

在Power Query M语言中，自定义函数内部递归调用自身时，必须使用@符号前缀来引用当前函数（因为函数定义尚未完成完全绑定）。你的代码中递归调用gatherpagingdata(updatedData, nextPageUrl)时缺少@，导致Power BI无法识别该函数名。同时原代码中newReq的获取逻辑存在问题：直接提取[pageDetails][nextPageUrl]会丢失items数据，无法完成数据合并。

修正后的完整代码 #

// NB Making calls to multiple instances from the same Power BI Dash can cause problems with authentication
// check data source security

    // Common parameters
    queryStringApiIntegrationCodevar = "xxxx",
    queryStringUserNamevar = "xxxxxx",
    queryStringUserSecretvar = "xxxxx",
    queryStringContentTypevar = "application/json",
    headers = [#"ApiIntegrationCode"=queryStringApiIntegrationCodevar, #"UserName"=queryStringUserNamevar, #"Secret"=queryStringUserSecretvar, #"Content-Type"=queryStringContentTypevar],
    apiUrl = "https://webservices4.autotask.net/ATServicesRest/V1.0/Projects/query",
    searchFilter = "{\"filter\":[{\"op\":\"gte\",\"field\":\"id\",\"value\":\"0\"}]}",

// Functions
converturl = (data as text) as text => 
let
    out = Uri.Parts("http://contoso?a=" & Text.Replace(data, "&", "%26"))[Query][a]
in
    out,

gatherpagingdata = (data as list, uri as text) =>
    let
        // 解码分页URL
        nextpagefunc = converturl(uri),
        textBeforePaging = Text.BeforeDelimiter(nextpagefunc, "?paging"),
        textAfterPaging = Text.BetweenDelimiters(nextpagefunc, "paging=", " "),

        // 获取完整API响应
        response = try Json.Document(Web.Contents(textBeforePaging, [Headers=headers, Query=[paging=textAfterPaging]])) otherwise error "Failed to retrieve data from the API Gather",
        newdata = response[items],
        nextPageUrl = response[pageDetails][nextPageUrl],

        // 合并新数据到集合
        updatedData = List.Combine({data, newdata}),

        // 递归调用（添加@符号引用自身）
        result = if nextPageUrl <> null then @gatherpagingdata(updatedData, nextPageUrl) else updatedData
    in
        result,

// Execute
    initReq = try Json.Document(Web.Contents(apiUrl, [Headers=headers, Query=[search=searchFilter]])) otherwise error "Failed to retrieve data from the API 1st Pass",
    initData = initReq[items],
    nextPageUrl = initReq[pageDetails][nextPageUrl],

    outputList = if nextPageUrl = null then 
        initData 
    else 
        gatherpagingdata(initData, nextPageUrl),
    
    expand = Table.FromRecords(outputList)
in
    expand

关键修改点 #

• 递归调用添加@符号：将gatherpagingdata(updatedData, nextPageUrl)改为@gatherpagingdata(updatedData, nextPageUrl)，确保Power BI能识别递归调用的函数。
• 修复响应获取逻辑：先获取完整的API响应对象，再分别提取items数据和nextPageUrl，解决数据丢失问题。
• 简化冗余代码：移除重复的URL解码步骤，优化逻辑结构。

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