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 to all!!!
I´m new in Power Bi, days ago I was able to create a connection to a Rest Api using the Web API connection option.
The API deliver 50 lines per reques, the entire data base is arroun 600 lines.
The API has 2 meta variables: {lastTimestamp} that is include in the URL and TotalCount.
Is it possible to configure the next steps in the Web API connector so I can set up Power bI to make many request to get all the data or should I move the connection to the blank query option?
The Original URL from the API is:
https://api.repsly.com/v3/export/clients/{lastTimestamp}
To get the firs set of lines I changed the {lastTimestamp} for a 0 as the next URL https://api.repsly.com/v3/export/clients/0
To get the next 50 lines I have to take the last LastTimeStamp record and replace it in the URL until the meta variable TotalCount is equal to 0
Any help will be more than apreciated!!!!!
Solved! Go to Solution.
Hello @Holguinmora
saw that you opened this topic twice...
as you passed me your credentials, I was able to develop something for your. I've tested it and it should work. Here the code
let
Final = List.Generate
(
()=>
[
Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/0", [Headers=[Accept="application/json"]])),
TotalCount = 1
],
each [TotalCount]>0,
each
[
Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/" & Text.From([Result][MetaCollectionResult][LastTimeStamp]), [Headers=[Accept="application/json"]])) ,
TotalCount = [Result][MetaCollectionResult][TotalCount]
],
each [Result][Clients]
),
FinalTable = Table.FromRecords(List.Combine(Final))
in
FinalTable
Copy paste this code to the advanced editor to see how the solution works
Give it a test and let us know
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Hello @Holguinmora
saw that you opened this topic twice...
as you passed me your credentials, I was able to develop something for your. I've tested it and it should work. Here the code
let
Final = List.Generate
(
()=>
[
Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/0", [Headers=[Accept="application/json"]])),
TotalCount = 1
],
each [TotalCount]>0,
each
[
Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/" & Text.From([Result][MetaCollectionResult][LastTimeStamp]), [Headers=[Accept="application/json"]])) ,
TotalCount = [Result][MetaCollectionResult][TotalCount]
],
each [Result][Clients]
),
FinalTable = Table.FromRecords(List.Combine(Final))
in
FinalTable
Copy paste this code to the advanced editor to see how the solution works
Give it a test and let us know
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Jimmy hi
thank you very much for your valuable help
The solution is clear and structured.
Please do not hesitate to contact me if you need any help
Hi Holguinmore,
I am not clear about your requirement, did you mean "a loop for fetching paginated data from a REST API"? If so, you could try to refer to this post and How To Do Pagination In Power Query for details.
By the way, I am not professional in API, you also could ask @ImkeF for more suggestions.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Zoe hi
I just sent @ImkeF a private mail, hope to get news from him
Do you j¡know if Microsof has some kind of service that I can pay and get help with this things? I contact many partners in Colombia, I got so many different answers and prices that is very hard to know if all this guys know a solution...
Hi Holguinmora,
I am sorry , I am not professional in this part, I suggest you also could open a ticket for this problem(https://powerbi.microsoft.com/en-us/support/), which is not for free.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.