REST api request in Power Query more than 1000 records

[julhelp]

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

[mahoneypat · Accepted solution]

Please see this video for a good approach to do this.

Power BI - Tales From The Front - REST APIs - YouTube

Pat

[julhelp]

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]

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]

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-n...

But i also dont know if i did this right:

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

[mahoneypat]

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]

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]

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.

[julhelp]

hi @v-yingjl ,

i tried to insert a RelativePath in my case. I understand how it should work theoretically I manage to implement it but not in my code. There i cant insert all my things from Web.Contents.

can someone help me? 

[freginier]

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

[julhelp]

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

[freginier]

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

[julhelp]

Excel Power Query but it´s similar to Power BI