'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Create Multiple Power Query Queries from a Large Text File in Excel
Question details
The user needs to extract data from a 22-million-row text file using Power Query and load the results into multiple separate Excel worksheets because the dataset size exceeds standard worksheet limits.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing and segmenting a massive text dataset for analysis in Excel without losing data.
- Observed behavior
- Excel worksheets have a strict limit of 1,048,576 rows, making it impossible to load the entire 22-million-row text file into a single sheet without data truncation.
Ensure you are running a 64-bit version of Office and have sufficient system RAM, as processing multi-million-row datasets in the Data Model is highly memory-intensive.
Load to the Data Model and Create Filtered Referenced Queries
Bypass the worksheet row limit by loading the entire dataset strictly as a connection to the Data Model, then create separate filtered queries to extract smaller chunks into standard worksheets.
By loading your massive text file directly to the Data Model, you avoid Excel's physical worksheet grid limits. Once the master connection is established, you can reference it multiple times to slice the data into manageable pieces.
Open Excel, navigate to the 'Data' tab on the ribbon, click 'From Text/CSV', and browse to select your 22-million-row text file.
In the preview window, click the drop-down arrow next to 'Load' and select 'Load To...'. Choose 'Only Create Connection' and check the box labeled 'Add this data to the Data Model'. Click 'OK'.
Open the 'Queries & Connections' pane on the right side of Excel. Right-click your newly created master connection and select 'Reference' to open the Power Query Editor with a linked query.
Apply filters to your columns (e.g., by date, category, or region) to ensure the resulting dataset contains fewer than 1,000,000 rows.
Click 'Close & Load To...' from the Home tab in the Power Query Editor. Select 'Table' and choose 'New worksheet'. Repeat the reference and filtering process for the remaining data.
Filter Data Directly During the Initial Import
If loading a pure connection for a massive file fails or stalls your system, filter the data in Power Query before completing the initial load to the Data Model.
Discover a Fast and Free Alternative for Your Everyday Spreadsheets
While extreme big-data tasks like processing 22-million rows are uniquely suited for specialized database tools or Excel's Data Model, WPS Office offers a completely free, lightning-fast, and highly compatible suite for your everyday data analysis and spreadsheet tasks. Enjoy a familiar interface without the heavy subscription costs.
- 1. Download the software: Visit the official WPS Office website and click the free download button.
- 2. Install WPS Office: Run the lightweight installer and follow the quick setup wizard.
- 3. Open your Excel files: Launch WPS Spreadsheet and instantly open your existing .xlsx and .csv files to continue working seamlessly.
Frequently Asked Questions
What is the absolute maximum row limit in an Excel worksheet?
An Excel worksheet is strictly limited to 1,048,576 rows and 16,384 columns. If you try to load a dataset larger than this directly into a standard worksheet, Excel will truncate the data and display a warning.
Can I analyze a 22-million-row dataset in Excel without splitting it?
Yes, you can bypass the worksheet row limit by loading the massive dataset exclusively into the Data Model (selecting 'Only Create Connection' and 'Add this data to the Data Model'). From there, you can use Power Pivot or PivotTables to aggregate and analyze the full dataset.
Why does my Power Query connection fail when loading large text files?
Connection failures on massive files often occur due to system memory (RAM) limitations, especially if you are using a 32-bit version of Excel. Upgrading to 64-bit Office or filtering the dataset inside the Power Query Editor before loading it can resolve these stability issues.
