Forum Discussion

julhelp's avatar
julhelp
Icon for Helper I rankHelper 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
julhelp's avatar
julhelp
Icon for Helper I rankHelper 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
Icon for Microsoft Employee rankMicrosoft 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
    Icon for Helper I rankHelper 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
      Icon for Microsoft Employee rankMicrosoft 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
        Icon for Helper I rankHelper 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...