Power Query - Zendesk API - Scheduled Refresh

[naelske_cronos]

Hello,

A couple of months ago I made a similar post about calling the Zendesk REST API. In Power Query M I have to work with pagination to get all the tickets from the very first time till now. The problem is, that it doesn't work with a simple pagination like page 2, page 3, etc... but with a unix start time as parameter. SEE SCREENSHOT.

I managed to do it for the REST API to get all the users because here it works with a next page. Before I began with my looping I counted how many pages I'd need based on the total count of users (here 9.068). In the API they speak about a 100 records per page, so 9.068 / 100 is around 90,68 pages (rounded up) that I'd need to get all my users.

My code that I used to get all the users.

    ZendeskAccessTokenJSON = Json.Document(ZendeskAccessTokenRequest),
    GetZendeskAccessToken = ZendeskAccessTokenJSON[access_token],
    HTTPHeader = [RelativePath="api/v2/users.json", Query=[page="1"], Headers = [#"Authorization"="Bearer " & GetZendeskAccessToken, #"Content-Type"="application/json"]],
    ZendeskAgentsRequest = Web.Contents("https://***.zendesk.com", HTTPHeader),
    ZendeskAgentsJSON = Json.Document(ZendeskAgentsRequest),
    ZendeskAgentsCount = ZendeskAgentsJSON[count],
    HowManyPages = Number.RoundUp(ZendeskAgentsCount / 100),
    Pagination = {1..HowManyPages},
    ChangeListIntoTable = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    GetPageNumber = (PageNumber as number) =>
let
    HTTPHeader = [RelativePath="api/v2/users.json", Query=[page=Number.ToText(PageNumber)], Headers = [#"Authorization"="Bearer " & GetZendeskAccessToken, #"Content-Type"="application/json"]],
    ZendeskAgentsRequest = Web.Contents("https://***.zendesk.com", HTTPHeader),
    ZendeskAgentsJSON = Json.Document(ZendeskAgentsRequest),
    ZendeskAgentsList = ZendeskAgentsJSON[users],
    ExpandListOfRecords = Table.FromRecords(ZendeskAgentsList)
in
    ExpandListOfRecords,
    AddForEachPageWantedTables = Table.AddColumn(ChangeListIntoTable, "Get Agents", each GetPageNumber([Column1]))
in
    AddForEachPageWantedTables

I want to manage the same thing for the tickets, to know beforehand how many pages I'd need but based on that timestamp or such. It's very important that it also can be refreshed in Power BI Service because sometimes Power BI Service sees the uses of web.contents as an unsupported source.

Kind regards

[artemus]

Sounds like you need to define a recursive function. Format would be like:

MyTable = 
let 
   GetRemainingData = (url as text, header) =>
   let
      WebResult = Web.Contents(url, header),
      ResultTable = ... //Transform data
      NextUrl = ... //Get next url
    in
      if NextUrl = null then
         ResultTable
      else
         Table.Union({ResultTable, @GetRemainingData(NextUrl, header)})
in
   GetRemainingData("https://myInitialUrl", myHeader)

Note the @ when calling the function

Also, if the next_page is not actually in the record use [next_page]? to get a null value if it isn't there

[naelske_cronos]

Hello @artemus 

Thank you very much for your help but I guess that was approximately the same thing I did in the past but these kind of functions don't pass through Power BI Service for a scheduled refresh. Or did your code passed the scheduled refresh in Power BI Service?

let
    ConvertLastTicketsIntoDateTimeZone = DateTime.AddZone(DateTime.FromText("01/01/1970 00:00:00"), 0),
    ConvertBeginDateIntoDateTimeZone = DateTime.AddZone(DateTime.FromText("01/01/1970 00:00:00"), 0),
    ConvertLastTicketsIntoUnix = Duration.TotalSeconds(ConvertLastTicketsIntoDateTimeZone - ConvertBeginDateIntoDateTimeZone),
    GetZDAccessToken = ZendeskAccessToken,
    GetURLParameter = (URL) =>
let
    HTTPHeader = [Headers = [#"Authorization"="Bearer " & GetZDAccessToken, #"Content-Type"="application/json"]],
    ZendeskIncrementalTicketsRequest = Web.Contents(URL, HTTPHeader),
    ZendeskIncrementalTicketsJSON = Json.Document(ZendeskIncrementalTicketsRequest),
    ZendeskIncrementalTicketsList = @ZendeskIncrementalTicketsJSON[tickets],
    IterationOverNextPage = if @ZendeskIncrementalTicketsJSON[count] = 1000 then @ZendeskIncrementalTicketsList & @GetURLParameter(ZendeskIncrementalTicketsJSON[next_page]) else @ZendeskIncrementalTicketsList
in
    IterationOverNextPage,
    GetAllZendeskIncrementalTickets = GetURLParameter("https://***.zendesk.com/api/v2/incremental/tickets.json?start_time=" & Number.ToText(ConvertLastTicketsIntoUnix) & "&include=metric_sets"),
    ExpandListOfRecords = Table.FromRecords(GetAllZendeskIncrementalTickets)
in
    ExpandListOfRecords

See https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9312540-make-functions-refreshabl...

Maybe someone managed to do this?

Kind regards

[artemus]

Maybe my usecase is different, but it seems to work fine for me.

Some things you can try:

1. If your allowed to do so, disable privacy checks in the options settings for this file.

2. Change your request to pass in the url parameters as part of the Query in the header:

 HTTPHeader = [Headers = [#"Authorization"="Bearer " & GetZDAccessToken, #"Content-Type"="application/json"], Query = [start_time=Number.ToText(ConvertLastTicketsIntoUnix), include="metric_sets"]]

#2 may work for you since it means you always have the same  url, just different query parameters.

[naelske_cronos]

Hello @artemus 

Thanks for your time to answer my questions but it's not a default pagination working with pages from 1 to 10 but with timestamps and you don't know which is the next timestamp or last timestamp URL the go to the next page. I managed to get all the data from all the pages with a function but the problem is the scheduled refresh in Power BI service. It doesn't support it.

I worked with another workaround: with MS Flow I used a loop to get all the pages and saved it in a file and with Power BI I accessed these pages and append it all the data together. Not the workaround I wanted but it works in Power BI service.

Kind regards