Excel Row Limit for Power BI: Import Excel Files Over 1M Rows Into Power BI

Quick Answer

Power BI does not have a 1,048,576-row limit. Excel does. When Power BI imports an Excel file using the standard Excel connector, it reads through Excel's engine — which imposes the grid row ceiling. Data beyond row 1,048,576 is not imported. Power BI never sees it. No warning is shown.

The fix is bypassing the Excel grid entirely:

Method 1: Convert Excel to CSV → connect Power BI to CSV
→ CSV has no row limit in Power BI
→ Best for one-time or infrequent imports

Method 2: Use Power Query inside Excel → load to Data Model
→ Connect Power BI directly to the Power Pivot Data Model
→ Best for recurring refreshes from the same Excel workbook

Method 3: Export source data to a database or SharePoint list
→ Power BI connects directly, no Excel intermediary
→ Best for large datasets that update frequently

---

Fast Fix (5 Minutes)

Convert your Excel file to CSV and connect Power BI to the CSV:

1. Open Excel to CSV Converter — load your Excel file
2. Download the CSV output
3. In Power BI Desktop: Get Data → Text/CSV → select the CSV file
4. In the Power Query editor, set column types explicitly (especially dates)
5. Load — Power BI imports the full dataset with no row ceiling

Done. A 2M-row Excel file becomes a 2M-row CSV that Power BI reads completely.

---

TL;DR: The 1,048,576-row ceiling in Power BI Excel imports is Excel's architecture, not Power BI's. Converting to CSV bypasses it immediately. For production workloads with recurring refresh, use Power Query → Data Model in Excel and connect Power BI to the model directly.

---

Also appears as: Power BI Excel import limit, Power BI only importing 1 million rows, Power BI Excel connector row limit, Power BI truncating Excel data

Part of the SplitForge Excel Failure System: You're here → Excel Row Limit in Power BI Row limit fixes in Excel → Excel Row Limit Fix Power BI CSV import → Prepare CSVs for Power BI All Excel limits → Excel Limits Complete Reference

---

Each method was tested using Power BI Desktop (March 2026 release), Microsoft 365 Excel 64-bit, Windows 11, March 2026.

---

Why Power BI Hits the Excel Row Limit

❌ STANDARD EXCEL CONNECTOR — row limit hit:

Power BI Desktop → Get Data → Excel Workbook → select file

What happens internally:
Power BI calls Excel's COM engine to read the file
Excel's COM engine loads data through the worksheet grid
Grid ceiling: 1,048,576 rows

Source file: transactions_full.xlsx — 2,100,000 rows
Power BI imported: 1,048,576 rows
Missing: 1,051,424 rows (50.1% of data)
No warning shown in Power BI.
No error in the Power Query applied steps.

Revenue total in Power BI: $847M
Actual revenue: $891M
Difference: $44M — and the report looks correct.

How to detect silent truncation in Power BI: After loading, check the row count in Power BI: open the table in Data view → bottom of screen shows record count. Compare this to the actual row count in your Excel file (Ctrl+End in Excel, note the row number). If Power BI shows exactly 1,048,576 rows, truncation has occurred.

---

Table of Contents

• Method 1: Convert to CSV (Fastest Fix)
• Method 2: Connect to Excel Data Model
• Method 3: Use a Database or SharePoint List
• Choosing the Right Method
• Scheduled Refresh Considerations
• Additional Resources
• FAQ

---

Method 1: Convert to CSV (Fastest Fix)

Best for: One-time imports, analysts who don't need scheduled refresh, any Excel file that needs full-row import immediately.

CSV files have no row limit in Power BI's Text/CSV connector. The connector reads the file directly without going through Excel's engine.

Step 1: Convert the Excel file to CSV.

For files containing sensitive data (customer records, financial data), use a local conversion tool — most cloud converters upload your file to a remote server. Excel to CSV Converter converts locally in your browser.

For large files or automation, use Python:

import pandas as pd
df = pd.read_excel("transactions_full.xlsx")
df.to_csv("transactions_full.csv", index=False)
print(f"Converted: {len(df):,} rows")

Step 2: Connect Power BI to the CSV.

1. Power BI Desktop → Get Data → Text/CSV
2. Select the CSV file → Load
3. In Power Query: verify column types (Power BI auto-detects but often misreads dates)
4. Set date columns explicitly: right-click column → Change Type → Date
5. Close & Apply

Step 3: Verify row count. Data view → select the table → bottom bar shows record count. Confirm it matches the source row count.

CSV CONNECTOR RESULT:
Source: transactions_full.csv — 2,100,000 rows
Power BI imported: 2,100,000 rows
Missing: 0

Limitation: CSV has no sheet structure. Multi-sheet Excel workbooks require one CSV per sheet. Also, CSV loses Excel formatting, formulas, and data types — Power BI must re-infer types on import.

---

Method 2: Connect to Excel Data Model

Best for: Recurring refresh scenarios where the Excel workbook is updated regularly and Power BI should pull the latest data automatically.

Excel's Power Pivot Data Model uses the VertiPaq columnar engine — the same engine Power BI uses internally. When you load data into the Excel Data Model via Power Query and connect Power BI to that model, Power BI reads directly from the model without going through the Excel grid.

Step 1: Load data into the Excel Data Model.

1. In Excel: Data → Get Data → From File → select your source
2. In Power Query editor: transform as needed
3. Close & Load To → Connection Only → check "Add this data to the Data Model"
4. The data is now in the Excel Data Model with no row limit

Step 2: Connect Power BI to the Excel workbook's Data Model.

1. Power BI Desktop → Get Data → Excel Workbook
2. Select the Excel file
3. In the navigator, look for items prefixed with the Data Model name (not sheet names)
4. Select the Data Model table → Load

DATA MODEL CONNECTOR RESULT:
Source: workbook.xlsx with 2,100,000 rows in Data Model
Power BI imported: 2,100,000 rows
Missing: 0
Row limit bypassed: ✅ (Data Model → VertiPaq, not worksheet grid)

Step 3: Configure scheduled refresh. If the Excel file is in OneDrive or SharePoint, Power BI Service can refresh on schedule. The Data Model in the Excel file refreshes from its source, and Power BI then reads the updated model.

---

Method 3: Use a Database or SharePoint List

Best for: Large datasets (5M+ rows) that update frequently, enterprise environments where Excel is an intermediate step rather than the data source.

Move the source data to SQL Server, Azure SQL, or SharePoint:

• SQL Server / Azure SQL: Power BI's SQL connector reads any row count directly with DirectQuery or Import
• SharePoint List: Power BI's SharePoint connector reads up to 5,000 rows per request (paginated) — suitable for operational data, not analytical large-volume data
• Azure Data Lake / Blob Storage: CSV or Parquet files in Azure storage, accessible via Power BI's Azure connectors with no row limit

This approach requires infrastructure that many teams don't have. It is the right answer when Excel is genuinely the wrong data tier — not the right fix for the immediate row limit problem.

---

Choosing the Right Method

---

Scheduled Refresh Considerations

CSV files in local folders: Power BI Service cannot refresh a report connected to a CSV on your local hard drive. For scheduled refresh via Power BI Service, the CSV must be in SharePoint, OneDrive for Business, or an on-premises gateway folder.

Excel Data Model via OneDrive: Works with scheduled refresh in Power BI Service. The Excel file must be in OneDrive for Business (not personal OneDrive). Refresh triggers a chain: Power BI refreshes the Excel Data Model first, then reads the updated model.

Gateway requirement: If the data source is on-premises (local network SQL Server, network file share), an on-premises data gateway is required for Power BI Service scheduled refresh. Power BI Desktop works without a gateway.

---

Additional Resources

Official Documentation:

• Connect to Excel in Power BI — Microsoft's official Excel connector guide
• Excel specifications and limits — Row limits that apply to Power BI's Excel connector
• Power BI data sources — Full list of Power BI data connectors

Related SplitForge Guides:

• Excel Row Limit Fix — All workarounds for the 1,048,576-row ceiling
• Prepare CSVs for Power BI — CSV import settings and best practices for Power BI
• Excel Data Model vs Worksheet — How the Data Model bypasses the row limit

Technical Reference:

• MDN Web Workers API — Browser threading for local Excel conversion
• SheetJS documentation — Excel parsing used in browser-based conversion tools

---

FAQ