KQL Query output limit of 5 lakh rows

Question

KQL Query output limit of 5 lakh rows

Jatin Kolhe 0

Hi , i have a kusto table which has more than 5 lakh rows and i want to pull that into power bi. When i run the kql query it gives error due to the 5 lakh row limit but when i use set notruncation before the query then i do not get this row limit error on power bi desktop but get this error in power bi service after applying incremental refresh on that table. My question is that will set notruncation always work and i will not face any error further for millions of rows and is this the only limit or there are other limits on ADE due to which i may face error due to huge volume of data or i should export the data from kusto table to azure blob storage and pull the data from blob storage to power bi. Which will be the best way to do it?

1 answer

Your answer

Answer 1

Venkat Reddy Navari 2,975 Microsoft External Staff Moderator

Hi @Jatin Kolhe The issue you're encountering stems from Azure Data Explorer's (Kusto) default query result limit, which restricts output to 5L rows or about 64 MB of data. While using set notruncation works in Power BI Desktop, it doesn’t always guarantee success in the Power BI Service, particularly when incremental refresh is applied.

Key Considerations:

set notruncation: This can help in development, but it doesn't remove all limitations. In Power BI Service, you may still hit constraints like execution timeouts, memory usage, or dataset size limits during refresh.
Export to Azure Blob Storage: Export large volumes from Kusto to Blob Storage (e.g., in Parquet format), then load the data into Power BI. This is generally more reliable and scalable.
Data Partitioning: Use time-based filters or break your query into smaller chunks to make data retrieval more manageable.
Optimize Queries: Review your KQL to ensure it’s efficient and minimizes resource usage.

Recommended Approach:

If you're working with millions of rows, exporting data to Azure Blob Storage and connecting Power BI to that source is typically the best practice especially for production dashboards with incremental refresh.

For more details on Kusto query limits, you can refer to the official documentation: Kusto Query Limits.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Venkat Reddy Navari 2,975 Reputation points Microsoft External Staff Moderator

2025-05-28T09:25:01.4933333+00:00

Hi @Jatin Kolhe Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful.
Jatin Kolhe (LTIMINDTREE LIMITED) 0 Reputation points Microsoft External Staff

2025-05-28T12:07:28.26+00:00

yes it was helpful, the only problem is we cannot store entire 100 million records kusto table data in blob since then we will have to pull 100 million records in power bi and then apply aggregation if required any in power bi which will not be good during refresh. So the only solution will be to store aggregated csvs/parquet file in blob and then pull them in power bi.
Venkat Reddy Navari 2,975 Reputation points Microsoft External Staff Moderator

2025-05-28T14:06:55.0233333+00:00
@Jatin Kolhe You're absolutely right, pulling raw 100 million rows into Power BI and aggregating there would be inefficient, especially during scheduled refreshes. Pre-aggregating the data and storing it as CSV or Parquet in Blob Storage is definitely the right approach for performance and scalability.

Suggestions to streamline the pipeline

Automate Aggregation + Export: If you're not already doing this, consider using a scheduled Kusto export job or Azure Data Factory to automate the aggregation and export to Blob on a regular cadence (e.g., daily or hourly).

Use Parquet Over CSV (If Possible): Parquet files offer better compression and faster load times in Power BI, especially for large datasets.

Incremental Updates: To avoid reprocessing the entire dataset, you can export only new aggregated data (e.g., by date) and append it to your storage. Then, use Incremental Refresh in Power BI to pick up just the changes.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful.
Venkat Reddy Navari 2,975 Reputation points Microsoft External Staff Moderator

2025-05-29T09:41:08.7533333+00:00

@Jatin Kolhe Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Jatin Kolhe (LTIMINDTREE LIMITED) 0 Reputation points Microsoft External Staff

2025-06-04T06:52:56.8266667+00:00

@Venkat Reddy Navari if we load the historical data one time in power query and disable refresh for that query, and create another query for incremental refresh for 1 day and schedule refresh of power bi report every hour then he problem of volume will never come unless your per day data is above 5 lakh rows.In that case also there must be some way to loop through the query in power query to fetch x hrs in the latest day in each loop, so it seems it is not required to move the data to blob storage.
Venkat Reddy Navari 2,975 Reputation points Microsoft External Staff Moderator

2025-06-04T11:41:58.9833333+00:00

@Jatin Kolhe Your approach of loading historical data once in Power Query with refresh disabled, combined with an incremental refresh query for daily data, is a practical way to handle large datasets and avoid the 5lakh row limit.

Regarding looping queries in Power Query to fetch smaller time slices (e.g., hourly), please note:

Power Query Limitations: It doesn’t support native looping or iterative queries. Instead, you can parameterize queries (using date/time filters) and orchestrate multiple refreshes via automation tools like Azure Data Factory or Power BI Dataflows if needed.

Performance & Partitioning: Breaking refreshes into smaller chunks can help stay within service limits but may increase refresh time and complexity. If daily data grows significantly, partitioning data at the source or pre-aggregating before loading into Power BI is recommended.

Power BI Service Limits: Be aware of refresh timeouts, memory usage, and dataset size constraints in Power BI Service. Very large or frequent refreshes might still pose challenges. Power BI Incremental Refresh

Blob Storage for Large Volumes: For very large datasets or production use, exporting pre-aggregated data to Azure Blob Storage (preferably in Parquet format) and connecting Power BI to that data remains a scalable best practice.

This incremental refresh strategy works well if daily data volumes are manageable. For larger volumes, combining incremental refresh with pre-aggregation and external storage offers better scalability and performance.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful.
Venkat Reddy Navari 2,975 Reputation points Microsoft External Staff Moderator

2025-06-05T10:34:29.2733333+00:00

@Jatin Kolhe Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

KQL Query output limit of 5 lakh rows

1 answer

Your answer