Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Solved! Go to Solution.
Just remove the code below.
& Options
Just remove the code below.
& Options
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |