To load data from a folder in Excel Power Query, use Data > Get Data > From File > From Folder, select the folder path, and then choose Combine & Load to merge all supported files into one table. This imports every file in that folder (and optionally subfolders) into a single query, which you can refresh later to pick up new or changed files.
Why Load from a Folder Instead of One File
Manually opening and copying data from multiple Excel workbooks, CSV files, or text files is slow and error-prone. The folder option in Power Query lets you treat a whole directory as a single data source. When you refresh the query, Power Query re-reads the folder contents and pulls in any files that were added, removed, or modified since the last refresh. This is especially useful for monthly reports, exported system logs, or data dumps that arrive as separate files with the same structure.
Power Query can read many file types from a folder, including .xlsx, .xls, .csv, .txt, .json, .xml, and .pdf (in Microsoft 365). It does not read .xlsb or password-protected files without additional steps. The folder connector works on Windows, Mac, and Excel for the web, though the ribbon path differs slightly: on Mac it is Data > Get Data > From File > From Folder, and on Excel for the web you use Data > Get Data > From Folder.
How the Folder Load Works Under the Hood
When you connect to a folder, Power Query creates a query that lists every file in that folder as a row. Each row contains metadata columns like Name, Extension, Date accessed, Date modified, Date created, Attributes, Folder Path, and a Content column that holds the binary file data. The Content column is what Power Query uses to parse each file into a table.
If you click Combine & Load (or Combine & Transform), Power Query analyzes the first file to detect the structure, then applies that same transformation to every file. It assumes all files have the same columns in the same order. If a file has extra columns or missing columns, you will get errors or misaligned data. The combine step uses a function called Sample File that you can edit to change how files are parsed.
Step-by-Step: Load All Files from a Folder
- In Excel, go to Data > Get Data > From File > From Folder.
- Browse to the folder that contains your files and click OK.
- In the preview window, you see a list of files. Click Combine & Load to merge them all, or Combine & Transform to open the Power Query editor first.
- If you choose Combine & Transform, Power Query asks you to pick a sample file. Select the first file in the list, then click OK.
- In the Power Query editor, review the transformations. You can remove columns, change data types, filter rows, or add custom columns.
- Click Close & Load to load the combined table into a new worksheet, or Close & Load To to choose a destination.
To include files in subfolders, click the Folder Path column filter and select all subfolders, or use a custom function that recursively reads subfolders. The default combine only reads files directly in the chosen folder.
Choosing Between Combine & Load and Combine & Transform
Combine & Load is the fastest option when all files are identical in structure and you don’t need to clean anything. It loads the merged data directly to a worksheet. Combine & Transform opens the Power Query editor so you can modify the transformations before loading. Use this when files have inconsistent column names, extra header rows, or need data type changes.
If you need to load only certain files, you can filter the file list before combining. For example, to load only .csv files, click the Extension column filter and uncheck everything except “.csv”. To load only files modified after a certain date, filter the Date modified column. You can also add a custom column to exclude files based on name patterns.
Common Problems and How to Fix Them
- Files with different column headers: Power Query uses the first file as the template. If other files have different headers, you get errors or null values. Solution: use Combine & Transform, then edit the
Sample Filefunction to promote headers dynamically, or use theTable.Combinefunction with a custom column that reads each file separately. - Blank rows or extra header rows: Use Remove Rows > Remove Top Rows or Remove Blank Rows in the Power Query editor before loading.
- Data type errors: Power Query guesses data types from the sample file. If a column contains numbers in some files and text in others, you may see errors. Set explicit data types in the editor, or use
tryexpressions in a custom column. - Cannot find folder path: If you move the folder, the query breaks. Edit the query’s source step to point to the new location, or use a relative path with
Excel.CurrentWorkbook()to reference a cell containing the path. - Too many files: Loading thousands of files can be slow. Use Data > Get Data > From File > From Folder and filter to only the needed files before combining. You can also use
Folder.Filesin a custom function to process files in batches.
Refreshing and Managing the Query
Once loaded, the combined table is a query that you can refresh manually by right-clicking the table and choosing Refresh, or automatically by setting the workbook’s refresh options. To change the folder path or transformations, go to Data > Queries & Connections, double-click the query name, and edit it in the Power Query editor.
To make the query refresh automatically when the workbook opens, go to Data > Queries & Connections, right-click the query, select Properties, and check “Refresh data when opening the file”. You can also set a refresh interval in the same dialog, but that only works while the workbook is open.
Frequently Asked Questions
Can I load from a folder without combining files?
Yes. Instead of clicking Combine & Load, click Load To and choose “Only Create Connection”. This creates a query that lists the files and their metadata, but does not parse the contents. You can then use that list as a reference in other queries, or load it as a table to see which files exist in the folder.
Does Power Query load files from subfolders automatically?
No. The default folder connector only reads files directly in the selected folder. To include subfolders, you need to either filter the Folder Path column to include subfolder paths (which appear as separate rows), or write a custom function that uses Folder.Files recursively. The easiest method is to select the folder that contains all subfolders, then in the file list, click the Folder Path column filter and check all subfolders you want.
What happens if a file is added to the folder after I load?
When you refresh the query, Power Query re-reads the folder and picks up any new files that match the filter criteria. If the new file has a different structure, the refresh may fail. To handle that, use Combine & Transform and edit the sample file function to be more flexible, or use a custom function that reads each file independently and then combines them with Table.Combine.
Can I load only specific columns from each file?
Yes. After combining, you can remove unwanted columns in the Power Query editor. To avoid loading unnecessary columns in the first place, you can edit the sample file step to select only the columns you need, and that transformation will apply to all files. For example, if you only need columns A and C, you can remove column B in the sample file step.
Why does my combined table show errors like “DataFormat.Error”?
This usually happens when a column in one file has a different data type than the sample file. For instance, if the sample file has a date column, but another file has text in that column, Power Query throws an error. Fix this by setting the column data type to Any in the sample file step, or by using a custom column that converts the value with try and otherwise.
Is there a way to load from a folder on OneDrive or SharePoint?
Yes. Use Data > Get Data > From File > From Folder and paste the URL of the OneDrive or SharePoint folder. Power Query will connect using your Microsoft account. The steps are the same as for a local folder, but you may need to sign in. Note that the folder must be accessible to your account, and the combine process works the same way.
Related
- Free Excel Loan Amortization Template: Get One Now
- Excel Learning Center Wilmington NC: Classes & Training
- Excel Just Numbers From a Cell? How to Extract Text
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Wedding Budget Spreadsheet UK: Build a Practical TrackerA UK wedding budget spreadsheet is a simple way to compare your available budget with estimated costs, supplier…
- How to Record an Entire PowerPoint PresentationTo record an entire presentation in PowerPoint, open it in the desktop app, choose Record > From Beginning,…
- Named Range Sheets: How Excel Scope WorksIn Excel, a named range is a label for a cell or range, and its scope determines whether…
- PowerPoint Audio Controls: Add and Customize PlaybackTo add audio controls to a PowerPoint slide, insert an audio file with Insert > Audio, select the…
- String Array Split in Java: Methods, Examples, and TipsIn Java, call String.split(regex) to divide text into a String[], such as String[] parts = text.split(","); for comma-separated…
- How to Double Space an Entire Document in Microsoft WordTo double-space an entire document in Microsoft Word, press Ctrl+A on Windows (Command+A on Mac), then choose Home…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.