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.
Hi,
My name is Julien, Business Analyst, its been few days that i try to Get data from a REST API - POST method with a query using M.
Here the query :
(StartDate as text)=> let Url = "https://public-api.ringover.com/v2/calls", Token = "here is my key", EntitiesPerPage = 100, StartDate = Date.ToText(Date.FromText(StartDate), "yyyy-MM-ddT00:00:00Z"), GetJson = (Url, LastIdReturned) => let Body = "{""intra"":false, ""limit_count"":" & Text.From(EntitiesPerPage) & ", ""start_date"":""" & StartDate & """, ""last_id_returned"":" & Text.From(LastIdReturned) & "}", Options = [Headers=[ #"Authorization" = Token ], Content=Text.ToBinary(Body)], RawData = Web.Contents(Url, Options), Json = Json.Document(RawData) in Json, GetEntityCount = () => let Json = GetJson(Url, 0), Count = Json[#"total_call_count"] in Count, GetPage = (LastIdReturned) => let Json = GetJson(Url,LastIdReturned), LastIdReturned = try List.Last(Json[#"call_list"])[cdr_id] otherwise 0, data = try Json[#"call_list"] otherwise null, res = [Data=data, Next=LastIdReturned] in res, NbRecords = GetEntityCount(), EntityCount = List.Max({ EntitiesPerPage, NbRecords }), PageCount = Number.RoundUp(EntityCount / EntitiesPerPage), GeneratedList = List.Generate( ()=>[i=0, res = GetPage(0)], each [i]<PageCount and [res][Data]<>null, each [i=[i]+1, res = GetPage([res][Next])], each [res][Data]), Table = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in Table
It's a bit complicated due to the pagination, and this query does not works and i not find why, actually its return the all lists but duplicates of the first one.
Here is the following link of Ringover for use there API but it does not help me neither : https://developer.ringover.com/
I have ask help from Ringover or colleagues but im still stuck.
If someone can help me, it will be wonderfull...
Thanks a lot
Julien
Solved! Go to Solution.
Hello @PhilipTreacy
Finaly my colleague Data Architect found the solution.
The LasIdReturned in GetPage row 24 "LastIdReturned = try List.Last(Json[#"call_list"])[cdr_id] otherwise 0," was in conflict with all the "LastIdReturned" before.
So he have rename all the "LastIdReturned before" by "LastId" and now it's works very well.
Thank to yo again for youur time.
BR
Julien
Hi @Julien7438
I'm a bit confused. Does this work or not? Is the issue just that you are getting the first list duplicated?
If that is the case then your initial conditions for res in List.Generate may need to be changed.
()=>[i=0, res = GetPage(0)],
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
Hi @PhilipTreacy ,
Thanks for your answer, the code don't work and do exactly what you say.
I tried different way using this topic but i can't figure that.
Your right the iteration part have an issue.
Thanks a lot, i will investigate in this way, if you have any suggestion, let me know.
BR,
Julien
Hi @Julien7438
Can you be specific about how it's not working. Is it generating an error? Is it giving you unexpected results? Can you share what output the query does generate?
regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
Hello @PhilipTreacy
Finaly my colleague Data Architect found the solution.
The LasIdReturned in GetPage row 24 "LastIdReturned = try List.Last(Json[#"call_list"])[cdr_id] otherwise 0," was in conflict with all the "LastIdReturned" before.
So he have rename all the "LastIdReturned before" by "LastId" and now it's works very well.
Thank to yo again for youur time.
BR
Julien
Hi Julien,
I've been trying to do the same thing as you. Not really a pro with PowerQuery.
Tried your solution but all I receive is duplicated entities. If I choose 100 entities per page, it fetch 100 entities and then duplicates them.
I tried the solution you published afterward but it changes nothing, maybe I didn't understand you. Can you copy&paste the new code or something?
My aim is to gather all the calls from Ringover from a selected startdate and then update new data everyday acumulatively.
I hope I can still reach you!
best regards!
Arturo
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.