Forum Discussion
vminvsky
4 years agoFrequent Visitor
Web.Contents Power Query Incremental Refresh Error
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 i...
- 4 years ago
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.
vminvsky
4 years agoFrequent Visitor
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.