Forum Discussion
Data model with JSON data (open data) is only showing 1000 rows
Hi everyone,
I would like to build a data model with an open data source (JSON). So far so good but I only see 1000 rows in my table after refreshing while the file contains about 14 million rows.
What could be the issue? It's the first time I'm using this method. I've looked around already to find the cause of this issue but haven't found a solution yet. Hopefully someone could help me out.
BR
Dennis
Screenshot
After searching a bit further I found the solution with this issue.
With the URL you've to enter the limit,
e.g. https://opendata.rdw.nl/resource/m9d7-ebf2.json?$limit=150000
In that you will get 150,000 rows out of the dataset which standard is showing only 1,000 rows in the JSON format.
I was struggling with this for some days but finally figured out how to retrieve the data. Thanks anyhow for thinking along with me!
BR
Dennis
4 Replies
Hi Anonymous,
In Power Query it doesn't go any further than row 1000 what implates there are only 1000 records available:
I just did a double check; when creating a card in the report I shows a count of 1000 as well.
Hi amitchandak, it's not about paginating, but getting more than 1000 rows out of the dataset.
BR
DennisAfter searching a bit further I found the solution with this issue.
With the URL you've to enter the limit,
e.g. https://opendata.rdw.nl/resource/m9d7-ebf2.json?$limit=150000
In that you will get 150,000 rows out of the dataset which standard is showing only 1,000 rows in the JSON format.
I was struggling with this for some days but finally figured out how to retrieve the data. Thanks anyhow for thinking along with me!
BR
Dennis
Anonymous , does it require pagination, if yes refer
A similar approach is needed -https://www.myonlinetraininghub.com/scrape-data-multiple-web-pages-power-query
