Forum Discussion

pdbenbow's avatar
pdbenbow
Resolver II
7 years ago
Solved

REST API Pagination - converting records to text?

I'm writing a report to pull ticket metrics from the Zendesk API. The API is paginated, with 100 records per page, so I need to iterate through all pages to return the metrics I need. The block of M ...
Need Help
v-chuncz-msft's avatar
v-chuncz-msft
Community Support
7 years ago

pdbenbow ,

 

Just remove the code below.

& Options
pdbenbow's avatar
pdbenbow
Resolver II
7 years ago

Thanks very much. Here's the final DAX solution for anyone interested:

 

let 
    BaseUrl = "https://yourorganization.zendesk.com/api/v2/ticket_metrics.json?",
    Token = "yourtoken",
    EntitiesPerPage = 100,
    Options = [Headers=[ #"Authorization" = "Basic " & Token ]],
    Url = BaseUrl,

    GetJson = (Url) =>
        let
            RawData = Web.Contents(Url, Options),
            Json = Json.Document(RawData)
        in
            Json,

    GetTotalCount = () =>
        let
            Json = GetJson(Url),
            Entities = Json[count]
        in
            Entities,

    EntityCount = GetTotalCount(),
    PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndex = { 1 .. PageCount},

    GetPage = (PageIndex) =>
        let
            PageUrl = BaseUrl & "page=" & Text.From(PageIndex),
            Json = GetJson(PageUrl),
            Value = Json[ticket_metrics]
        in
            Value,

    GetUrl = (PageIndex) =>
        let
            PageNum = "page=" & Text.From(PageIndex),
            PageUrl = BaseUrl & PageNum
        in
            PageUrl,

    Urls = List.Transform(PageIndex, each GetUrl(_)),
    Pages = List.Transform(PageIndex, each GetPage(_)),
    DataList = List.Union(Pages),

    TableFromList = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    TableFromList
  • CGlembocki's avatar
    CGlembocki
    Frequent Visitor
    6 years ago
    pdbenbow

    Thanks for posting the final code. It is really helpful to see the final solution.

    I was wondering whether Url could be combined with BaseUrl?

    Also, are the Urls and Pages combined to create the Source in the TablesfromList?

    Glembi
    • pdbenbow's avatar
      pdbenbow
      Resolver II
      6 years ago

      Glembi,

       

      Url and BaseUrl can be consolidated, yes. That's just some redundancy in my code.

       

      And yes, my interpretation of how the M is operating is like you said: it's iterating through the page index, building each URL to retrieve each page, and then unioning all the pages together.  The final step just takes the preceding operations (Source) and splits them.

       

      Pete