Forum Discussion

julhelp's avatar
julhelp
Helper I
4 years ago
Solved

REST api request in Power Query more than 1000 records

Hi,

 

i am facing the challange of requesting data from a REST service that limits the records by 1000. But i have data with more than 100.000 records. I am pretty new in power bi but my idea was to solve this problem by using an offset. The problem is, that i dont know how to use exactly an offset in my case.

 

All the resarches i did, didnt help me out yet. 

 

This is my code now:

The problem is still that i am getting just 1000 records.

 

Thanks for the help

 

Need Help

12 Replies

  • freginier's avatar
    freginier
    Solution Sage
    4 years ago

    Do you have the same result if you send the request from postman or a browser ? 

    • julhelp's avatar
      julhelp
      Helper I
      4 years ago

      I can get the total of the records as number but if i request the data i just get 1000.  

      • freginier's avatar
        freginier
        Solution Sage
        4 years ago

        Sorry but It's Power BI API or other application API ? 

    • julhelp's avatar
      julhelp
      Helper I
      4 years ago

      Thanks this was the video i searched for hours 🙂 

       

      My next problem is, that i am getting my data but as record. So there is an error massage saying:  

      Expression.Error: We cannot apply operator & to types Record and Text.
      Details:
      Operator=&
      Left=[Record]
      Right=&limit=1000&offset=

       

      so i dont know how du convert the [Record] in my Web.Contents to text.

       

      this is my changed code so far:

       

      and this my whole code: 

       

       

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        4 years ago

        That error is because your are combining the Headers record with the "&limit= ..." text. You need to provide the info in that record in the same way as the limit and offset.

         

        ...?x-P4t-Auth-User=" & Username & "&x-P4t=" & AuthToken & "&limit= ...

         

        Alternatively, you can provide the limit and offset in the first term of Web.Contents

         

        Web.Contents(BaseUrl & "/" & tabellaname & "limit=1000&offset=" & [Column1], ...)

         

        Pat