REST API Pagination - converting records to text?

[pdbenbow]

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 code below shows how I'm approaching this, and I feel like I'm 95% finished. However, when I run this block, I'm getting the following error message: 

Expression.Error: We cannot apply operator & to types Text and Record.

• Operator=&
• Left=https://yourorganization.zendesk.com/api/v2/ticket_metrics.json?
• Right=Record

I've added Text.From() in several spots where I felt it was necessary in order to convert any records to text, but I still can't get this to work. I'm guessing there's an intermediate step I'm forgetting in order to bridge the gap between the records I'm returning and the text that I need to invoke the API sequentially.

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

    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) & Options,
            Json = GetJson(PageUrl),
            Value = Json[data]
        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)
in
    DataList

[v-chuncz-msft · Accepted solution]

@pdbenbow ,

Just remove the code below.

& Options

[pdbenbow]

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]

@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]

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