cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Configure Web Api Connection Several Request

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
Highlighted
Community Support
Community Support

Re: Configure Web Api Connection Several Request

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.

Highlighted
Helper I
Helper I

Re: Configure Web Api Connection Several Request

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 

 

 

Highlighted
Community Support
Community Support

Re: Configure Web Api Connection Several Request

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.

Highlighted
Super User I
Super User I

Re: Configure Web Api Connection Several Request

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

Highlighted
Helper I
Helper I

Re: Configure Web Api Connection Several Request

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors