Fix Excel Power Query Loading Only 500,000 Rows from Azure
Question details
Users experience a limitation where Power Query only loads a maximum of 500,000 rows into an Excel worksheet when connected to Azure Log Analytics.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Importing a massive dataset from Azure Log Analytics into Microsoft Excel using Power Query.
- Observed behavior
- The Power Query connection successfully imports data but truncates the results at exactly 500,000 rows, despite the source containing more records and the Excel worksheet supporting over one million rows.
Verify that your Azure Log Analytics workspace has the complete dataset available and ensure your version of Excel supports the Data Model (Power Pivot) feature before modifying connection settings.
Load Data Directly to the Excel Data Model
Bypass worksheet visualization limitations by loading the massive dataset directly into the Excel Data Model for PivotTable analysis.
Loading millions of rows directly into a single Excel worksheet is highly inefficient and can cause performance issues. By loading the data directly to the Data Model or a PivotTable, you can analyze massive datasets seamlessly without hitting query preview caps or worksheet rendering limits.
In the Power Query Editor, click on the 'Home' tab, then click the drop-down arrow under 'Close & Load' and select 'Close & Load To...'.
In the Import Data dialog box, select the 'Only Create Connection' radio button to prevent data from rendering on the worksheet grid.
Check the box at the bottom that says 'Add this data to the Data Model' and click 'OK'.
Go to the 'Insert' tab on the Excel ribbon, select 'PivotTable', and choose 'Use this workbook's Data Model' as your data source to analyze the full dataset.
Adjust Azure Log Analytics Export Limits
Investigate and adjust the Azure API limitations or query parameters to ensure the source is not restricting the output to 500,000 rows.
Analyze Standard Datasets with WPS Office Spreadsheet
If you are struggling with complex Microsoft Excel data connectors, API limits, and expensive subscription costs, WPS Office offers a free, lightweight, and highly compatible spreadsheet alternative. Easily analyze large CSVs or local database exports with a familiar, lightning-fast interface.
- 1. Download and Install WPS Office: Visit the official WPS website to download the free suite and install it on your device.
- 2. Open WPS Spreadsheet: Launch the application and select Spreadsheet to begin working with your standard datasets.
- 3. Import Your Data: Go to the Data tab and choose Import Data to bring in your large CSV or local database files for rapid analysis.
Frequently Asked Questions
What is the maximum number of rows Excel can handle?
A single Excel worksheet is limited to 1,048,576 rows. However, by using the Excel Data Model (Power Pivot), you can load and analyze hundreds of millions of rows, bypassing the traditional worksheet grid limit entirely.
Why does my Azure Log Analytics query stop at exactly 500,000 records?
The Azure Log Analytics REST API has a hard-coded export limit per query. Regardless of the analytical tool you use to fetch the data, the API will only return a maximum of 500,000 rows or 64MB of data per single request.
Can I load Power Query data directly into a PivotTable?
Yes. When closing your Power Query Editor, select 'Close & Load To...', and choose 'PivotTable Report'. This allows you to summarize and visualize massive datasets without ever loading the raw rows into a worksheet.
