Forum Discussion

Holguinmora's avatar
Holguinmora
Helper I
6 years ago
Solved

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

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

5 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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

    • Holguinmora's avatar
      Holguinmora
      Helper I
      6 years ago

      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's avatar
    dax
    Community Support
    6 years ago

    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.

    • Holguinmora's avatar
      Holguinmora
      Helper I
      6 years ago

      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 

       

       

      • dax's avatar
        dax
        Community Support
        6 years ago

        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.