Power BI中Web.Contents函数内失效但外部正常的问题排查

架构老林

2026-7-4

问题：Power M分页调用API时出现405 Method Not Allowed错误

我在调用Autotask API的Power M代码中，当initReq不为空时会进入gatherpagingdata函数进行分页查询。该函数从initReq[pageDetails][nextPageUrl]获取动态URL，后续分页URL则来自newReq[pageDetails][nextPageUrl]。我已确认uri、headers及相关文本参数均为文本类型，但函数内执行Web.Contents时出现405错误，外部直接调用却能正常运行，求排查原因。

错误代码

let
    // NB Making calls to multiple instances from the same Power BI Dash can cause problems with authentication
    // https://www.youtube.com/watch?v=fstsQMZiHME
    // check data source security

    // Common parameters
    queryStringApiIntegrationCodevar = "xxxxxx",
    queryStringUserNamevar = "xxxxxxxxx",
    queryStringUserSecretvar = "xxxxxxxx",
    queryStringContentTypevar = "application/json",
    
    headers = [
        #"ApiIntegrationCode"=queryStringApiIntegrationCodevar,
        #"UserName"=queryStringUserNamevar,
        #"Secret"=queryStringUserSecretvar,
        #"Content-Type"=queryStringContentTypevar
    ],

    apiUrl = "https://webservices4.autotask.net/ATServicesRest/V1.0/Projects/query",
    apiUrlnew = "https://webservices4.autotask.net/ATServicesRest/V1.0/Projects/query/next",
    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, headers) =>
        let
            // Get the Data
            // Replace Percent URL-encoded characters
            nextpagefunc = converturl(uri) as text,        
            textBeforePaging = Text.BeforeDelimiter(nextpagefunc, "?paging=") as text,
            textAfterPaging = Text.BetweenDelimiters(nextpagefunc, "?paging=", " ") as text,

            newReq = Json.Document(Web.Contents(textBeforePaging, [Headers=headers, Query=[paging=textAfterPaging]])),
            newdata = newReq[items],

            // Add that data to rolling aggregate
            updatedData = List.Combine({data, newdata}),

            // Check for the next URL using function
            nextPageUrl = newReq[pageDetails][nextPageUrl],

            // If there's no next page of data, return. If there is, call @gatherpagingdata again to get more data
            result = if nextPageUrl <> null then @gatherpagingdata(updatedData, nextPageUrl, headers) 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],

    // Before we call gather(), we want to see if it's even necessary.
    // First request returns only one page? Return.
    // OutputList = try if initReq[pageDetails][nextPageUrl] = null then initData else gather(initData, BaseURI) otherwise error uri,
    uri = initReq[pageDetails][nextPageUrl],

    // Decode the extracted value
    // Replace Percent URL-encoded characters
    nextpagefunc = converturl(uri),

    outputList = 
        if initReq[pageDetails][nextPageUrl] = null then 
            initData 
        else 
            gatherpagingdata(initData, uri, headers),

    // Then place records into a table. This will expand all columns available in the record.
    expand = Table.FromRecords(outputList)
in
    expand

错误信息

DataSource.Error: Web.Contents failed to get contents from
'https://webservices4.autotask.net/ATServicesRest/V1.0/Projects/query/next?paging=%7B%22pageSize%22%3A500%2C%22previousIds%22%3A%5B3%5D%2C%22nextIds%22%3A%5B593%5D%7D%26search%3D%7B%22filter%22%3A%5B%7B%22op%22%3A%22gte%22%2C%22field%22%3A%22id%22%2C%22value%22%3A%220%22%7D%5D%7D'
(405): Method Not Allowed Details:
DataSourceKind=Web
DataSourcePath=https://webservices4.autotask.net/ATServicesRest/V1.0/Projects/query/next
Url=https://webservices4.autotask.net/ATServicesRest/V1.0/Projects/query/next?paging=%7B%22pageSize%22%3A500%2C%22previousIds%22%3A%5B3%5D%2C%22nextIds%22%3A%5B593%5D%7D%26search%3D%7B%22filter%22%3A%5B%7B%22op%22%3A%22gte%22%2C%22field%22%3A%22id%22%2C%22value%22%3A%220%22%7D%5D%7D

可正常运行的代码

let
    // NB Making calls to multiple instances from the same Power BI Dash can cause problems with authentication
    // https://www.youtube.com/watch?v=fstsQMZiHME
    // check data source security

    // Common parameters
    queryStringApiIntegrationCodevar = "xxxxx",
    queryStringUserNamevar = "xxxxx",
    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",
    apiUrlnew = "https://webservices4.autotask.net/ATServicesRest/V1.0/Projects/query/next",
    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, headers, searchFilter) =>
        let
            // Get the Data
            // Replace Percent URL-encoded characters
            nextpagefunc = converturl(uri) as text,        
            textBeforePaging = Text.BeforeDelimiter(nextpagefunc, "?paging=") as text,
            textAfterPagingandBeforesearch = Text.BetweenDelimiters(nextpagefunc, "?paging=", "&search=") as text,
            textAfterPagingandsearchEquals = Text.BetweenDelimiters(nextpagefunc, "&search=", " ") as text,
            // pull out search filter

            newReq = Json.Document(Web.Contents(textBeforePaging, [Headers=headers, Query=[paging=textAfterPagingandBeforesearch , search=textAfterPagingandsearchEquals]])),
            newdata = newReq[items],

            // Add that data to rolling aggregate
            updatedData = List.Combine({data, newdata}),

            // Check for the next URL using function
            nextPageUrl = newReq[pageDetails][nextPageUrl],

            // If there's no next page of data, return. If there is, call @gatherpagingdata again to get more data
            result = if nextPageUrl <> null then @gatherpagingdata(updatedData, nextPageUrl, headers , searchFilter) else updatedData
        in
            result,

    // Execute
    
    apiUrltest = "https://webservices4.autotask.net/ATServicesRest/V1.0/Projects/query/next",
    searchFiltertest = "{""pageSize"":500,""previousIds"":[3],""nextIds"":[593]}",
    searchFiltertest2 = "{""filter"":[{""op"":""gte"",""field"":""id"",""value"":""0""}]}",

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

    // Before we call gather(), we want to see if it's even necessary.
    // First request returns only one page? Return.
    // OutputList = try if initReq[pageDetails][nextPageUrl] = null then initData else gather(initData, BaseURI) otherwise error uri,
    uri = initReq[pageDetails][nextPageUrl],

    // Decode the extracted value
    // Replace Percent URL-encoded characters
    nextpagefunc = converturl(uri),

    outputList = 
        if initReq[pageDetails][nextPageUrl] = null then 
            initData 
        else 
            gatherpagingdata(initData, uri, headers , searchFilter),

    // Then place records into a table. This will expand all columns available in the record.
    expand = Table.FromRecords(outputList)
in
    expand

问题原因与解决方案

核心问题

错误代码中的gatherpagingdata函数只提取了paging参数，但忽略了URL中附带的search参数。Autotask的/query/next接口需要同时接收paging和search两个参数才能正确处理请求，缺少search参数会导致接口返回405 Method Not Allowed。

从错误信息的URL可以看到，实际请求的URL包含&search=xxx，但错误代码中仅传递了paging参数，相当于丢弃了search参数，导致接口无法识别请求的合法性。

修复方案

参考可正常运行的代码，修改gatherpagingdata函数，确保同时传递paging和search两个参数：

给gatherpagingdata函数添加searchFilter参数，保证分页请求能携带原始搜索条件
在函数内正确拆分URL中的paging和search参数值
调用Web.Contents时，将两个参数同时传入Query参数中

修改后的关键函数部分：

gatherpagingdata = (data as list, uri as text, headers, searchFilter) =>
    let
        nextpagefunc = converturl(uri) as text,        
        textBeforePaging = Text.BeforeDelimiter(nextpagefunc, "?paging=") as text,
        textAfterPagingandBeforesearch = Text.BetweenDelimiters(nextpagefunc, "?paging=", "&search=") as text,
        textAfterPagingandsearchEquals = Text.BetweenDelimiters(nextpagefunc, "&search=", " ") as text,

        newReq = Json.Document(Web.Contents(textBeforePaging, [Headers=headers, Query=[paging=textAfterPagingandBeforesearch , search=textAfterPagingandsearchEquals]])),
        newdata = newReq[items],

        updatedData = List.Combine({data, newdata}),
        nextPageUrl = newReq[pageDetails][nextPageUrl],
        result = if nextPageUrl <> null then @gatherpagingdata(updatedData, nextPageUrl, headers , searchFilter) else updatedData
    in
        result

同时，调用gatherpagingdata时需传入searchFilter参数：

outputList = 
    if initReq[pageDetails][nextPageUrl] = null then 
        initData 
    else 
        gatherpagingdata(initData, uri, headers, searchFilter),

额外优化建议

使用Uri.Parts解析URL的Query参数，比文本拆分更可靠，避免编码处理错误：

urlParts = Uri.Parts(uri),
pagingParam = urlParts[Query][paging],
searchParam = urlParts[Query][search]

检查Power BI数据源权限设置，确保允许动态URL调用（可在数据源设置中选择"忽略隐私级别设置"或正确配置隐私级别）。

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

Power BI中Web.Contents函数内失效但外部正常的问题排查

错误代码 #

错误信息 #

可正常运行的代码 #

问题原因与解决方案 #

核心问题 #

修复方案 #

额外优化建议 #