Web.Contents Power Query Incremental Refresh Error

[vminvsky]

I'm having some issue refreshing using the Power Query function Web.Contents().

I need to pull data from an API which limits to 100 results per page. I created a custom function that is able to iterate through pages until the next page is empty.

    GetPage = (url, path) =>
        let Json  = GetJson(url, path),
            TestUrl = Text.From(Json[#"paging"][#"next"]),
            UrlEnd = Text.Range(TestUrl, n),
            Value = Json[#"data"],
            response = if TestUrl <> null then List.Combine({Value, @GetPage(BaseUrl, UrlEnd)}) else Value
        in  response, 

    GetJson = (Url, Path) =>
        let Options = [Headers=[ #"Authorization" = "Bearer " & Token ], RelativePath = Path],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,

When I conduct the refresh in Power BI Desktop I'm able to do the refresh. But when I publish the report to Power BI Server I get the following error: 

"This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources."

I went through a few tutorials on how to fix this issue:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/ 

https://community.powerbi.com/t5/Service/Dynamic-Web-Contents-Power-BI-Refresh-Error/m-p/714526 

All pages give the recommendation of using the RelativePath. I started using this as evident from code above, but it still raises the same error. For my base url I have tried https://eu.bill.dotyk.cloud/nua-barcelona/. 

The url path is then: 

Orders?IncludeItems=false&IncludeTransactions=false&api-version=2020-01&CreatedAfterTimeSpan=-6.00%3A00%3A00 

I realize there is a query parameter in Web.Contents() which I have tried. But this did not work. 

Any input would be great.

Venia

[vminvsky · Accepted solution]

Thank you! I was actually a little confused how to put api-version in the query because of the hyphen.

The solution was acutally a little different from what you recommend. What I ended up doing was passing in the URL directly, not into a function. Now the function looks like

GetJson = (offssett) =>
            let Options = 
            [
                Headers=[ #"Authorization" = "Bearer " & Token ], 
                Query = 
                [
                    IncludeItems="true",
                    IncludeTransactions="true",
                    Limit="100",
                    Offset=offssett,
                    CreatedAfter= Text.From(RangeStart) 
                ]
            ],
                RawData = Web.Contents("https://eu.bill.dotyk.cloud/nua-barcelona/Orders?api-version=2020-06-01", Options),
                Json    = Json.Document(RawData)
            in  Json,
        
        Json = GetJson(BaseUrl, UrlPath),
        Value = Json[#"data"],

        GetPage = (offssett) =>
            let Json  = GetJson(Text.From(offssett)),
            TestUrl = if Json[#"paging"] <> null then Text.From(Json[#"paging"][#"next"]) else null,
                Value = Json[#"data"],
                response = if TestUrl <> null then List.Combine({Value, @GetPage(offssett + 100)}) else Value
            in  response, 

But now I'll also fix the api-version. 

[lbendlin]

You're nearly there.  Your relative path should be "orders"  but everything else (everything after the question mark) needs to go in the "query" portion.

Something like this

let
    Source = Json.Document(Web.Contents("https://eu.bill.dotyk.cloud/nua-barcelona",[RelativePath="Orders",Query=[IncludeItems="false",IncludeTransactions="false",#"api-version"="2020-01",CreatedAfterTimeSpan="-6.00:00:00"]]))