Power BI连接Autotask API：动态数据源刷新失败求助

我正在开发一套对接Power BI和Autotask CRM系统的代码，本地运行正常，但上传至云端后出现**"Unable to schedule refresh of dynamic data source"**错误。我本来没打算设置定时刷新，只需要按需手动更新就行，但还是碰到了这个问题。我确定是动态URL导致的问题，但因为API分页调用必须这么做，不知道该怎么解决，求有经验的人帮忙，谢谢。

编辑：我已经参考过相关视频，但还是没搞明白哪里漏了。

原始代码 #

let

// {"filter":[{"id":"gte","value":"0"}]}

// searchFilter = "{"filter":[{"id":"gte","value":"0"}]}",
// {"filter":[{"op":"gte","field":"id","value":"0"}]}
searchFilter = "{"filter":[{"op":"gte","field":"id","value":"0"}]}",

searchParam = Uri.EscapeDataString(searchFilter),
headers = [Headers = [
                #"ApiIntegrationCode"="xxxxxxx",
                #"UserName"="xxxxxxx",
                #"Secret"="xxxxxxx",
                #"Content-Type"="application/json"]],
baseuri = "https://webservices4.autotask.net/ATServicesRest/V1.0/Contracts/query?search=" & searchParam,

initReq = Json.Document(Web.Contents(baseuri, headers)),
    initData = initReq[items],
    //We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records, 
    //then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then
    //create a table from those records
    gather = (data as list, uri) =>
        let
            //build new uri 
            newUri = Json.Document(Web.Contents(uri, headers))[pageDetails][nextPageUrl],
            //get new req & data
            newReq = Json.Document(Web.Contents(newUri, headers)),
            newdata = newReq[items],
            //add that data to rolling aggregate
            data = List.Combine({data, newdata}),
            //if theres no next page of data, return. if there is, call @gather again to get more data
            check = if newReq[pageDetails][nextPageUrl] = null then data else @gather(data, newUri)
        in check,
    //before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
    outputList = if initReq[pageDetails][nextPageUrl] = null then initData else gather(initData, baseuri),
    //then place records into a table. This will expand all columns available in the record.
    expand = Table.FromRecords(outputList),
    #"Sorted Rows" = Table.Sort(expand,{{"id", Order.Descending}})
in
    #"Sorted Rows"

修改后代码（检查数据源安全性） #

let
// NB Making calls to multiple instances from the same Power BI Dash can cause problems with authentication

    // 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"}]}",
        

//initReq = Json.Document(Web.Contents(apiUrl, headers)),
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(apiUrl, headers)) otherwise error apiUrl,

initData = initReq[items],
    //We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records, 
    //then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then
    //create a table from those records
    gather = (data as list, uri) =>
        let
            //build new uri
            // newUri = Json.Document(Web.Contents(uri, headersgatherfunc))[pageDetails][nextPageUrl],
            newUri = try Json.Document(Web.Contents(uri, [Headers=headers, Query=[search=searchFilter]]))[pageDetails][nextPageUrl] otherwise error "Failed to retrieve data from the API 2nd Pass",
            //get new req & data
            newReq = try Json.Document(Web.Contents(newUri, [Headers=headers, Query=[search=searchFilter]])) otherwise error "Failed to retrieve data from the API 3rd Pass",
            newdata = newReq[items],
            //add that data to rolling aggregate
            data = List.Combine({data, newdata}),
            //if theres no next page of data, return. if there is, call @gather again to get more data
            check = if newReq[pageDetails][nextPageUrl] = null then data else @gather(data, newUri)
        in check,
    //before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
    //outputList = try if initReq[pageDetails][nextPageUrl] = null then initData else gather(initData, BaseURI)otherwise error uri,
    outputList = try if initReq[pageDetails][nextPageUrl] = null then initData else gather(initData, apiUrl)otherwise error "Failed outputList",
    //then place records into a table. This will expand all columns available in the record.
    expand = Table.FromRecords(outputList)
in
    expand

最新代码（仍需排查gatherpagingdata函数报错） #

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

    // Common parameters
    //texttoconv = "dddd",
    //text = texttoconv as text,
    queryStringApiIntegrationCodevar = "xxxxx",
    queryStringUserNamevar = "xxxxxxxx",
    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

newURIwhatisit = (uri) =>
        let
            //build new uri search string

            newURIwhatisitfuncget = try Json.Document(Web.Contents(uri, [Headers=headers, Query=[search=searchFilter]]))[pageDetails][nextPageUrl] otherwise error "New Func URL Get",      

            //Lets get the search string
            // Text.BetweenDelimiters("111 (222) 333 (444)", "(", ")"),
            //nextPageUrlStaticfunc = Text.BetweenDelimiters(newUridata, " ", "?"),
            //queryStringnewfunc = Text.BetweenDelimiters(newUridata, "?", " "),
            //pagingnewvarfunc = Text.BetweenDelimiters(newUridata, "?", "pageSize"),
            //pageSizenewvarfunc = Text.BetweenDelimiters(newUridata, "pageSize", "previousIds"),
            //previousIdsnewvarfunc = Text.BetweenDelimiters(newUridata, "previousIds", "nextIds"),
            //nextIdsnewvarfunc = Text.BetweenDelimiters(newUridata, "nextIds", "search"),
            //searchnewvarfunc = Text.BetweenDelimiters(newUridata, "nextIds", "filter")
            
            //queryStringnew = Text.BetweenDelimiters(outputListgather, "?", " "),
            //nextPageUrlStaticsearchpaging = Text.BetweenDelimiters(newURIwhatisitfuncget, "paging=", " "),
            nextPageUrlStaticsearchsearch = Text.BetweenDelimiters(newURIwhatisitfuncget, "paging=", " "),
            newURIwhatisitfunc = nextPageUrlStaticsearchsearch

        in newURIwhatisitfunc,

newURIdatagather = (uri, nextpagefunc) =>
       let
            //build new uri
            newURIdatafuncget = try Json.Document(Web.Contents(uri, [Headers=headers, Query=[paging=nextpagefunc]]))[pageDetails][nextPageUrl] otherwise error "New Func URL",
            newURIdatafunc = newURIdatafuncget[items]
        in newURIdatafunc,       

gatherpagingdata = (data as list, uri) =>
    let
        // Get the Data
        newReq = try newURIdatagather(uri, nextpage) otherwise error "Failed to retrieve data from the API",
        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],
        //nextpagefunc = newURIwhatisit(nextPageUrl),
        nextpagefunc = newURIwhatisit(nextPageUrl),

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




//Execute
    //initReq = Json.Document(Web.Contents(apiUrl, headers)),
    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(apiUrl, headers)) otherwise error apiUrl,
    initData = initReq[items],


    //before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
    //outputList = try if initReq[pageDetails][nextPageUrl] = null then initData else gather(initData, BaseURI)otherwise error uri,
    nextPageUrl = initReq[pageDetails][nextPageUrl],
    outputList = try if initReq[pageDetails][nextPageUrl] = null then initData else gatherpagingdata(initData, nextPageUrl)otherwise error "Failed outputList",
    //then place records into a table. This will expand all columns available in the record.
    expand = Table.FromRecords(outputList)
in
    expand

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