How to Combine Large CSV Files with Power Query and Power Pivot
Question details
The user needs to combine multiple large CSV files exceeding one million rows and perform data transformations like lookups, error filtering, and duplicate removal.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Merging massive datasets from multiple CSV files for data analysis without hitting standard worksheet row limits.
- Observed behavior
- Worksheet formulas are inadequate and crash or fail for handling over a million rows; the goal is to successfully import, clean, and model this large dataset using background data modeling engines.
Ensure all your CSV files are stored in a single, dedicated folder and share the exact same column structure and headers before initiating the import process.
Combine, Clean, and Load CSV Files Using Power Query and Power Pivot
The most efficient way to handle datasets exceeding Excel's one-million-row limit is by combining them in Power Query, applying necessary transformations, and loading the result directly into the Data Model.
Power Query is specifically designed to handle large-scale data transformation much faster than traditional worksheet formulas. By processing the data in the background and loading it straight to Power Pivot, you bypass physical spreadsheet limitations entirely.
Go to the Data tab on the Excel ribbon, click 'Get Data' > 'From File' > 'From Folder'. Browse to the folder containing your CSV files and click 'Open'. On the preview window, click 'Transform Data' to open the Power Query Editor.
In the Power Query Editor, locate the Content column and click the 'Combine Files' icon (two downward arrows) in its header. Verify the sample data formatting in the popup and click 'OK' to automatically append all files together.
Navigate to the 'Add Column' tab to create any needed custom columns (like text extraction). For lookups, go to the Home tab and click 'Merge Queries' to join your main dataset with your supplementary lookup tables.
To handle #N/A errors, click the filter dropdown on the relevant column and select 'Remove Errors', or isolate them using 'Keep Errors' in a duplicate query. To remove duplicates based on column G, select the column header, right-click, and choose 'Remove Duplicates'.
On the Home tab, click the 'Close & Load' dropdown and select 'Close & Load To...'. In the dialog box, select 'Only Create Connection' and check the box labeled 'Add this data to the Data Model'. Click 'OK'.
Go to the Insert tab, click 'PivotTable', and select 'From Data Model'. You can now build PivotTables to analyze your massive, multi-million row dataset smoothly.
Need a Lightweight Office Suite for Everyday Data Analysis?
While advanced Power Query and Data Model features are specific to Microsoft Excel, WPS Office provides a blazing-fast, lightweight, and completely free alternative for standard data analysis, charting, and daily reporting. If you frequently handle standard spreadsheets and want a seamless, cost-effective solution, WPS Office is an excellent choice.
Frequently Asked Questions
Why use Power Query instead of standard worksheet formulas like VLOOKUP?
Standard worksheet formulas process data very slowly on massive datasets and are strictly limited to Excel's 1,048,576 row limit. Power Query operates in the background, handles millions of rows effortlessly, and automates the cleaning process without bloating your workbook's file size.
How do I separate rows with errors instead of just deleting them?
In the Power Query Editor, duplicate your main query. In the first query, select the column with errors, go to 'Home' > 'Keep Rows' > 'Keep Errors' to isolate them into their own table. In the second query, use 'Remove Rows' > 'Remove Errors' to keep only the clean data.
What is the Excel Data Model and why should I load data into it?
The Excel Data Model is an in-memory database engine (Power Pivot) integrated directly into Excel. It heavily compresses large datasets and allows you to build relational data models and PivotTables on data that far exceeds standard worksheet row limits.
Can I combine CSV files if they have slightly different column names?
When using the automated 'Combine Files' feature, Power Query aligns data strictly based on matching column headers. If the headers differ, you must import the files as separate queries, manually rename the headers so they match exactly, and then use the 'Append Queries' function on the Home tab.
