Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm back with the scheduled refresh for the REST API of Zendesk. For the ease of it I want to have the data of page 90 and page 91. After page 91 there's no next page or data afterwards. When I use a static URL as in my example, it takes too long before my query is evaluated and it results in a stack overflow...
let
GetURLParameter = (URL) =>
let
ddd = Text.AfterDelimiter(URL, "=", {0, RelativePosition.FromEnd}),
HTTPHeader = [RelativePath="api/v2/users.json", Query=[page=ddd], Headers = [#"Authorization"="Bearer 4acdc9aBLUH", #"Content-Type"="application/json"]],
ZendeskAgentsRequest = Web.Contents("https://BLUH.zendesk.com", HTTPHeader),
ZendeskAgentsJSON = Json.Document(ZendeskAgentsRequest),
ZendeskGroupsList = @ZendeskAgentsJSON[users],
IterationOverNextPage = try @ZendeskGroupsList & @GetURLParameter(ZendeskAgentsJSON[next_page]) otherwise @ZendeskGroupsList
in
IterationOverNextPage,
GetAllZendeskGroups = GetURLParameter("https://BLUH.zendesk.com/api/v2/users.json?page=90"),
ExpandListOfRecords = Table.FromRecords(GetAllZendeskGroups),
GetSelectedColumns = Table.SelectColumns(ExpandListOfRecords, {"id", "name", "email", "created_at", "updated_at", "time_zone", "iana_time_zone", "phone", "locale", "organization_id", "role", "active", "last_login_at", "ticket_restriction", "default_group_id"})
in
GetSelectedColumns
When I use the next code with a scalar value (dsss) as the URL of Web.Contents, it works perfectly but the problem is that it doesn't work in Power BI Service for the scheduled refresh because the URL is not supported.
let
GetURLParameter = (URL) =>
let
ddd = Text.AfterDelimiter(URL, "=", {0, RelativePosition.FromEnd}),
dsss = Text.Replace(URL, Text.AfterDelimiter(URL, "com"), ""),
HTTPHeader = [RelativePath="api/v2/users.json", Query=[page=ddd], Headers = [#"Authorization"="Bearer 4acdc9aBLUH", #"Content-Type"="application/json"]],
ZendeskAgentsRequest = Web.Contents(dsss, HTTPHeader),
ZendeskAgentsJSON = Json.Document(ZendeskAgentsRequest),
ZendeskGroupsList = @ZendeskAgentsJSON[users],
IterationOverNextPage = try @ZendeskGroupsList & @GetURLParameter(ZendeskAgentsJSON[next_page]) otherwise @ZendeskGroupsList
in
IterationOverNextPage,
GetAllZendeskGroups = GetURLParameter("https://BLUH.zendesk.com/api/v2/users.json?page=90"),
ExpandListOfRecords = Table.FromRecords(GetAllZendeskGroups),
GetSelectedColumns = Table.SelectColumns(ExpandListOfRecords, {"id", "name", "email", "created_at", "updated_at", "time_zone", "iana_time_zone", "phone", "locale", "organization_id", "role", "active", "last_login_at", "ticket_restriction", "default_group_id"})
in
GetSelectedColumns
Can someone help me why it doesn't work, both of them?
Kind regards
Hello,
I tried to do it like this: I just changed the try and otherwise to an if and else. If I ask for the last 20 pages, it evaluates it perfectly but if I ask for all the pages (90 pages) it keeps evaluating without giving a stack overflow. It keeps running...
let
param = Text.AfterDelimiter(URL, "=", {0, RelativePosition.FromEnd}),
HTTPHeader = [RelativePath="api/v2/users.json", Query=[page=param], Headers = [#"Authorization"="Bearer " & GetZendeskAccessToken, #"Content-Type"="application/json"]],
ZendeskGroupsRequest = Web.Contents("https://BLUH.zendesk.com", HTTPHeader),
ZendeskGroupsJSON = Json.Document(ZendeskGroupsRequest),
ZendeskGroupsList = @ZendeskGroupsJSON[users],
IterationOverNextPage = if ZendeskGroupsJSON[next_page] = null then ZendeskGroupsList else List.Union({ZendeskGroupsList, @GetURLParameter(ZendeskGroupsJSON[next_page])})
in
IterationOverNextPage,
GetAllZendeskGroups = GetURLParameter("https://BLUH.zendesk.com/api/v2/users.json?page=1"),
ExpandListOfRecords = Table.FromRecords(GetAllZendeskGroups),
GetSelectedColumns = Table.SelectColumns(ExpandListOfRecords, {"id", "name", "email", "created_at", "updated_at", "time_zone", "iana_time_zone", "phone", "locale", "organization_id", "role", "active", "last_login_at", "ticket_restriction", "default_group_id"}),
If someone can answer to this, this can, I guess, resolve the problem with the execution of the Web.Contents-function inside a function.
Kind regards
Hi @naelske_cronos ,
We can set timeout in power query like this.
Web.Contents("web url", [Timeout=#duration(0, 0, 0, 30)])
Hello,
Thanks for your answer, but doesn't that mean that it is just going to stop my execution? That's not really the solution to my problem?
Kind regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |