Forum Discussion
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 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
4 Replies
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- 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
