Power BI JSON file 1000 row limit

Question

Power BI JSON file 1000 row limit

A K 45

Hi all,

I am facing issues with Power BI only pulling in 1000 rows.

I have exported JSON data into azure data lake storage gen 2, which then our business analyst was able to pull in the files and create separate tables in a pbix file.

jsondata

The issue he has now come across is that it will only pull in 1000 rows for any table and we have not found a work around for it.

Any tips or feedback would be greatly appreciated.

Thank you in advance!

Hari Babu Vattepally 3,270 Reputation points Microsoft External Staff Moderator

2024-10-10T08:50:41.8766667+00:00

@A K,

Just following up to see if the above answer helped. Please do consider clicking 'Accept Answer'/'upvote' as accepted answers help community as well.
Hari Babu Vattepally 3,270 Reputation points Microsoft External Staff Moderator

2024-10-14T15:56:40.6+00:00

@A K,

We are checking to see if the above answer helped. Please do consider clicking 'Accept Answer'/'upvote' as accepted answers help community as well.
A K 45 Reputation points

2024-10-16T09:06:32.3533333+00:00

Hi Hari,

Thank you for your input and apologies for the late response. The issue came from the reporting API having a default page size set to 1000 so the data that has been extracted only had 1000 rows.
Hari Babu Vattepally 3,270 Reputation points Microsoft External Staff Moderator

2024-10-16T09:39:01.01+00:00

Hi @A K,

Thanks for the response and update. We are good to know that you got the root cause, and we understand that this is by design. Please let us know that the provided Answer is Helpful, if yes, please 'Accept' the Answer and do 'Upvote' the Answer. This will be helpful for the other community members as well. If you have any further queries, please feel free to contact Q&A. We will be glad to assist with the resolution.

Accepted answer

0 additional answers

Your answer

Hari Babu Vattepally 3,270 Reputation points Microsoft External Staff Moderator

2024-10-10T08:50:41.8766667+00:00

@A K,

Just following up to see if the above answer helped. Please do consider clicking 'Accept Answer'/'upvote' as accepted answers help community as well.
Hari Babu Vattepally 3,270 Reputation points Microsoft External Staff Moderator

2024-10-14T15:56:40.6+00:00

@A K,

We are checking to see if the above answer helped. Please do consider clicking 'Accept Answer'/'upvote' as accepted answers help community as well.
A K 45 Reputation points

2024-10-16T09:06:32.3533333+00:00

Hi Hari,

Thank you for your input and apologies for the late response. The issue came from the reporting API having a default page size set to 1000 so the data that has been extracted only had 1000 rows.
Hari Babu Vattepally 3,270 Reputation points Microsoft External Staff Moderator

2024-10-16T09:39:01.01+00:00

Hi @A K,

Thanks for the response and update. We are good to know that you got the root cause, and we understand that this is by design. Please let us know that the provided Answer is Helpful, if yes, please 'Accept' the Answer and do 'Upvote' the Answer. This will be helpful for the other community members as well. If you have any further queries, please feel free to contact Q&A. We will be glad to assist with the resolution.

Answer 1

Hi @A K,

Greeting! Thanks for reaching out to Microsoft Q&A.

It looks like you are encountering the issue with the 1000 row limit when working with JSON files in Power BI. This is a common challenge due to the design and payload size of JSON files. Here are some insights and potential solutions:

Payload Volume: The issue often arises because the JSON file has a large payload, making it difficult to profile and expand columns beyond 1000 rows.
Recommended Solutions:
- Use Mashup: This involves using Power Query to transform and combine data from multiple sources, which can help manage large JSON files.
- Change Format to JSON Lines: JSON Lines format can be more efficient for processing large datasets as each line is a valid JSON object.
- Convert JSON to CSV: Converting the JSON file to a CSV format can make it easier to handle and profile in Power BI.
Profiling Subset of Data: When profiling data, it's essential to view a subset to ensure the data is correct before processing. This can help manage large payloads more effectively.

Hope it helps!

If you have any specific questions or need further assistance, feel free to ask!

Please 'Upvote (thumps up)' if it helped, so that it can help others in the community looking for help on similar topics.

Share via

Power BI JSON file 1000 row limit

0 additional answers

Your answer