Power Query - Zendesk API - Scheduled Refresh - PART 2

[naelske_cronos]

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

[naelske_cronos]

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

[v-frfei-msft]

Hi @naelske_cronos ,

We can set timeout in power query like this.

Web.Contents("web url", [Timeout=#duration(0, 0, 0, 30)])

[naelske_cronos]

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