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 t...
Need Help
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

julhelp's avatar
julhelp
Helper I
4 years ago

thanks i think i did it right and that helped:

 

 

new problem: 

Formula.Firewall: Query 'Test (2)' (step 'Gefilterte Zeilen') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

I tried this but still dont works: https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-so-it-may-not/m-p/18637#M5539

 

But i also dont know if i did this right:

I have chosen the line in yellow to "outsource" it. 

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    Hard to tell without seeing the M code for TabelleName, Username, and Token (I assume those are other queries), but I would hard-code those in (instead of referencing them) to confirm it is working like that. You can then reference them one at a time to focus on the one that needs to use the approach described in that link.

     

    Pat

    • julhelp's avatar
      julhelp
      Helper I
      4 years ago

      that helped 🙂 

       

      the problem was the query where i am getting the total number of records.

       

      But still i have an error:

      DataSource.Error: Web.Contents failed to get contents from '.../api/v1/tables/S012&limit=1000&offset=0' (501): Not Implemented
      Details:
      DataSourceKind=Web
      DataSourcePath=.../api/v1/tables/S012&limit=1000&offset=0
      Url=.../api/v1/tables/S012&limit=1000&offset=0

       

      what means "not implementet"? 

       

      this is my code: 

       

       

      I think it is a problem with the limit and the offset. Because if i am not using them, everythink is fine. But then i just get 1000 records.

       

      I hope you or anyone else can help me here...

       

       

      • v-yingjl's avatar
        v-yingjl
        Community Support
        4 years ago

        Hi julhelp ,

        As far as I know, 501 not implementet belongs to Server Error which means that 'Expect this when some requested operation isn't implemented'.

         

        Whether try to use RelativePath in Web.Contents to modify it and check again, please refer:

        Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 

         

        In addition, you can also refer this simliar thread which introduces how to use custom function to paginate API record after getting the totalPage number in details:

        How to get paginated data from API in Power BI 

         

        Best Regards,
        Community Support Team _ Yingjie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.