Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Holguinmora
Helper I
Helper I

Configure Web Api Connection Several Request

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!!!!!

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

View solution in original post

5 REPLIES 5
Jimmy801
Community Champion
Community Champion

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

dax
Community Support
Community Support

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...

 

@dax 

 

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors