none
Power Query - Zendesk API - Scheduled Refresh - Dynamic Web.Contents RRS feed

  • Question

  • Hello,

    I have a question about Power Query M and dynamic function with the usage of Web.Contents. I have a total of 90 pages as next_page links, I guess not that much. Basically I iterate over each next_page link and get the number, so I can work with Web.Contents as suppose without having problems with scheduled refresh in Power BI Service.

    The problem is that if I'm iterating over those 90 pages, it keeps running, it keeps evaluating without giving any error. I did before but with another workaround but similar and it gave me a stack overflow but now it keeps running. If I'm iterating over 20 pages, but over 90 pages it's too much.

    let
        param = Text.AfterDelimiter(URL, "=", {0, RelativePosition.FromEnd}),
        HTTPHeader = [RelativePath="api/v2/users.json", Query=[page=param], Headers = [#"Authorization"="Bearer fekefk65...", #"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"})
    in
        GetSelectedColumns

    Can someone help me with another workaround and not with a parameter like = page = {1..90}. I'm more than sure that this could work but the problem about the evaluation is the only thing that's in the way.

    Kind regards

    Monday, December 16, 2019 2:03 PM

Answers

  • Hi,

    Have you confirmed that this step works as intended, for example by intentionally loading an unexisting page? One of my apps that gets web contents can sometimes keep going for a long time when the page it's trying to get isn't working. 

     IterationOverNextPage = if ZendeskGroupsJSON[next_page] = null then ZendeskGroupsList else List.Union({ZendeskGroupsList, @GetURLParameter(ZendeskGroupsJSON[next_page])})

    Concerning the scheduled refresh, have you tried putting the Web.Contents in a function and calling the function? I use this and scheduling works for me

    let
    
    WebCall = (URL) => 
    let
        Source = Web.Contents("https://INSERTHERE.com/api", [RelativePath=URL])
    in
        Source
    in WebCall

    Wednesday, December 18, 2019 7:48 AM
  • Hi naelske. I'm not sure why clearing the cache would cause things to start working. Perhaps the cache was "thrashing" (i.e. newly cached values were causing older values to be purged, causing those older values to be re-fetched the next time they were requested). This could explain the slow perf you were seeing. But it's difficult to say for sure.

    Ehren

    Monday, January 6, 2020 8:26 PM
    Owner

All replies

  • Hi there. When you say it "keeps running", do you mean that it never ends?

    Ehren

    Monday, December 16, 2019 10:53 PM
    Owner
  • Hello,

    Yes. I know I can work with a time-out but I want to know why it keeps running like that? 

    It's not that I'm going to get a lot of data. With this query I'm importing maximum 10.000 rows. When I use like:

    • page = "https://BLUH.zendesk.com" and pass it into Web.Contents(page, HTTPHeader) it works like a charm but the problem is that is isn't static and Power BI Service doesn't recognize it as a supported data source for scheduled refresh.
    • When I use it like I do know, it's perfect for the scheduled refresh but it keeps running with no result...


    Kind regards


    • Edited by naelske Tuesday, December 17, 2019 8:45 AM
    Tuesday, December 17, 2019 8:08 AM
  • Hi,

    Have you confirmed that this step works as intended, for example by intentionally loading an unexisting page? One of my apps that gets web contents can sometimes keep going for a long time when the page it's trying to get isn't working. 

     IterationOverNextPage = if ZendeskGroupsJSON[next_page] = null then ZendeskGroupsList else List.Union({ZendeskGroupsList, @GetURLParameter(ZendeskGroupsJSON[next_page])})

    Concerning the scheduled refresh, have you tried putting the Web.Contents in a function and calling the function? I use this and scheduling works for me

    let
    
    WebCall = (URL) => 
    let
        Source = Web.Contents("https://INSERTHERE.com/api", [RelativePath=URL])
    in
        Source
    in WebCall

    Wednesday, December 18, 2019 7:48 AM
  • Hello,

    There's no problem with the scheduled refresh or the looping.

    If I'm changing the page from GetAllZendeskGroups = GetURLParameter("https://BLUH.zendesk.com/api/v2/users.json?page=1") to GetAllZendeskGroups = GetURLParameter("https://BLUH.zendesk.com/api/v2/users.json?page=85") it takes a couple of seconds and I get my desired data. With Fiddler, I trace the API calls and I get 5 calls from 85 till 90 and that's good, but when I'm starting from the beginning, page 1 then it keeps loading, without even calling the API's? It doesn't even start my function?

    Kind regards

    Thursday, December 26, 2019 10:51 AM
  • Hello,

    So, it looks like I managed to get all the pages.

    In the options tab of Power BI, at "load data" I cleared the cache? I had like 2.16 gigabytes and max was 4 gigabytes? It is possible to explain what the cache has to do with why it was a problem to call my data from the REST API?

    Kind regards

    Thursday, December 26, 2019 12:05 PM
  • Hi naelske. I'm not sure why clearing the cache would cause things to start working. Perhaps the cache was "thrashing" (i.e. newly cached values were causing older values to be purged, causing those older values to be re-fetched the next time they were requested). This could explain the slow perf you were seeing. But it's difficult to say for sure.

    Ehren

    Monday, January 6, 2020 8:26 PM
    Owner