Data model with JSON data (open data) is only showing 1000 rows

[Anonymous]

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

[amitchandak]

@Anonymous , does it require pagination, if yes refer

A similar approach is needed -https://www.myonlinetraininghub.com/scrape-data-multiple-web-pages-power-query

[pranit828]

Hi @Anonymous 

The buffer size may be set to 1000. It will load 1000 at one go and when you scroll to the bottom. it will load more.

Can you try creating a report on this dataset and take the total count in a card.

You should `get the exact count.

[Anonymous]

Hi @pranit828, 

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
Dennis

[Anonymous · Accepted solution]

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