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
I´m trying to connect to the next API using Blank Query but it’s been more complex that I thought. Is it possible for anyone to give me a hand or contact me to so I can Pay an be able to get the code code to create this connection?
On the next lines the info from the API, any help will be more than apreciate!!!
On the next lines the API info:
Method: GET Path: https://api.repsly.com/v3/export/clients/{lastTimestamp}
Accept: application/json, application/xml
Content-Type: application/json, application/xml
User: 804144F2-78E3-47DE-9CCB-9B226634CD91
Password: 3B5A34D3-AD6D-496E-9125-4CAD9F8FXXXX
In each call the Api sends 50 lines - data base is no less than 600 lines.
To get the first set of data I change the {lastTimestamp} for a 0 as the next example https://api.repsly.com/v3/export/clients/0
On the next lines a short description on how I the Metadata must be implemented:
In every response you will receive meta variable LastTimeStamp of the last changed record in the list. Save it for future requests so you can use it as a parameter {lastTimeStamp} for every subsequent request until the response meta variable TotalCount is equal to 0, which means you have received all updates.
To get the complete list of clients, start your request with parameter {lastTimeStamp} = 0.
In this link the API manual: https://cdn2.hubspot.net/hubfs/391043/APIv3DeveloperDocumentation%20(4).pdf
In this link the field description: https://api.repsly.com/v3/help/
Any help will be more than apreciated!!!
Solved! Go to Solution.
Hello @Holguinmora
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
were you able to solve the problem with any reply given?
If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
All the best
Jimmy
If you use a scheduled refresh for this API, you may need to make the following alteration. Credit to Chris Webb.
Final = List.Generate
(
()=>
[
Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/", [RelativePath="0", Headers=[Accept="application/json"]])),
TotalCount = 1
],
each [TotalCount]>0,
each [
Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/", [RelativePath= Text.From([Result][MetaCollectionResult][LastTimeStamp]), Headers=[Accept="application/json"]])),
TotalCount = [Result][MetaCollectionResult][TotalCount]
],
each [Result][Clients]
)
Thank you @Jimmy801
Hello @Holguinmora
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
Oh joy! This works perfectly!!!! Thank you so much!!!!
Hello @Holguinmora
we can have a try.
For sure List.Generate is needed to loop through all timestamps till TotalCount = 0.
I'm not able to test it, because I don't have access.
You could contact me with a private message
All the best
Jimmy
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.