Yes, Excel Power Query can combine or load data from every file in a folder using Data > Get Data > From File > From Folder. This feature reads all supported files (Excel, CSV, text, JSON, PDF, etc.) in a chosen folder and its subfolders, letting you preview, filter, and transform the data before loading it into your workbook.
What the From Folder Command Actually Does
When you select From Folder, Power Query does not simply import the first file. It creates a table listing every file in the folder, with columns like Name, Extension, Date accessed, Date modified, Date created, Attributes, and a Folder Path column. You then have two main choices:
- Load – loads that file list as a table in your worksheet.
- Transform Data – opens the Power Query Editor, where you can combine the files’ contents, filter the list, or add custom columns.
The real power is the Combine Files button that appears in the preview. When you click it, Power Query automatically creates a function that reads each file, applies the same transformations to all of them, and stacks the results into one table. This is the most common way people use the feature.
How to Get Data From a Folder (Step by Step)
- In Excel for Microsoft 365 on Windows, go to Data > Get Data > From File > From Folder.
- Browse to the folder you want, or paste the full path, then click OK.
- A dialog shows a preview of the file list. If you only want to see the list, click Load. To combine the contents, click Transform Data.
- In the Power Query Editor, click Combine Files (usually on the Home tab).
- Choose a sample file in the next dialog – Power Query uses it to detect the structure (delimiters, headers, sheet names).
- Click OK. Power Query generates a query that reads every file, applies the same steps, and combines the rows.
- Click Close & Load to put the combined data into a new worksheet, or Close & Load To to choose the destination.
On a Mac, the ribbon path is the same in recent versions, but the dialog windows look slightly different. Excel for the web does not support the From Folder feature; you would need to upload files to a SharePoint folder and use Get Data > From SharePoint Folder if available.
Choosing the Right File Types and Folder Structure
From Folder works best when all files share the same structure. For example, if you have monthly sales reports with the same columns (Date, Region, Sales, Product), the combine step will line them up perfectly. If files differ – one has an extra column or a different header row – you will get errors or misaligned data. To avoid this:
- Keep all source files in one folder (or a consistent subfolder structure).
- Ensure the first row of each file contains the same headers.
- Use the same file type (e.g., all .xlsx or all .csv). Mixing types can confuse the sample file step.
- If you need to include subfolders, the file list includes a Folder Path column, but the combine function only reads files in the top-level folder by default. You can filter the list to include subfolders by adding a custom column or using the Folder Path in a filter.
Common Problems and How to Fix Them
- “We couldn’t find any files” – Check the folder path, permissions, and that files are not open in another program.
- Files with different structures – Use the Transform Sample File step to handle variations, or use a custom function that reads each file and applies conditional logic.
- Data types change after refresh – Power Query often guesses column types from the first file. If later files have numbers stored as text, you may see errors. Set explicit data types in the query (e.g.,
Table.TransformColumnTypes) to force consistency. - Too many files – Combining thousands of files can slow down the query. Consider filtering the file list before combining (e.g., only .csv files, or files modified after a certain date).
- Hidden files or system files – The file list includes hidden files; you can filter them out by checking the Attributes column.
Refreshing Data When New Files Arrive
One of the biggest advantages is that the query is dynamic. When you add a new file to the folder, you can refresh the query (right-click the query in the Queries & Connections pane and choose Refresh, or press Ctrl+Alt+F5 on Windows, Cmd+Option+F5 on Mac). The new file will be read and its data appended. This works because the query stores the folder path, not a static list of files. However, the refresh only works if the folder path remains the same and the file structure is unchanged.
If you move the workbook to another computer, you may need to update the folder path in the query. You can do this by editing the Source step in the Power Query Editor.
Comparing From Folder with Other Import Methods
Before Power Query, users often copied and pasted data from each file manually, or used VBA loops. The From Folder feature is far more efficient because it is repeatable and refreshable. Compared to Get Data > From File > From Workbook (which imports a single file), From Folder handles multiple files at once. Compared to Get Data > From Text/CSV, it can process many files without writing a separate query for each.
Another alternative is Get Data > From Azure Blob Storage or From SharePoint Folder if your files are stored in the cloud. These work similarly but require a connection to the cloud service. For local folders, From Folder is the simplest and most direct method.
Frequently Asked Questions
Can I combine only certain files in the folder? Yes. After clicking Transform Data, filter the file list before clicking Combine Files. For example, filter the Extension column to only .csv, or use a date filter on Date modified.
What if my files have multiple sheets? When you combine, Power Query asks you to choose a sample file and then a sheet or table. It will use that same sheet from all files. If you need to combine multiple sheets from each file, you would need a more advanced custom function that loops through sheets.
Does From Folder work with PDF files? Yes, in Excel for Microsoft 365, Power Query can extract tables from PDFs. When you combine, the sample file dialog lets you choose which PDF table to use. However, PDF extraction can be inconsistent if the layout varies.
Why does my combined query show errors like “Invalid cell value”? This usually happens when a file has a different column count or data type. Check the Changed Type step in the query and ensure all files have the same headers. You can delete that step and let Power Query keep everything as text, then convert types later.
Can I load the file list without combining? Yes, simply click Load instead of Transform Data. The resulting table will have file names, paths, dates, and sizes, which you can use for documentation or as a reference.
How do I update the folder path after moving files? In the Power Query Editor, click the Source step, then change the folder path in the formula bar or the dialog that appears. After closing, refresh the query.
Performance and Safety Tips
Combining a large number of files can be memory-intensive. To keep things fast, avoid loading unnecessary columns and filter out files you don’t need. Also, if your files are in a shared network folder, make sure you have read permissions and that the folder isn’t locked by another process.
When working with sensitive data, be aware that the query stores the folder path and the data in the workbook. If you share the workbook, others may see the data and the path. To protect privacy, you can remove the source step or use a relative path if the folder is in the same location as the workbook, but Power Query doesn’t support relative paths natively. A workaround is to use a parameter for the folder path and set it to a cell value, but that requires a bit of M coding.
Always save a copy of your workbook before making major query changes, because refreshing or editing a query cannot be undone with Ctrl+Z.
Related
- Excel Group of Schools Zimbabwe: Full Guide
- Excel Gradebook Template for Teachers: How to Use
- Excel Group Lake Jackson TX: What It Is & Services
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Delete Duplicate Lines in ExcelTo delete duplicate lines (rows) in Excel for Microsoft 365 on Windows, select a cell in your complete…
- Copy an Entire Excel Spreadsheet to a New FileTo copy an entire Excel spreadsheet into a separate file, open the workbook in Excel for Microsoft 365…
- Excel Table Themes: Styles, Colors, and How to Change ThemIn Excel, “table themes” usually means the built-in table styles that control a table’s colors, borders, and visual…
- Autofill in Excel Based on Text: Practical MethodsTo autofill text patterns in Excel for Microsoft 365 on Windows, type one or two examples in the…
- Microsoft Word Compare Documents Not Responding? FixesIf Microsoft Word’s Compare Documents command is not responding, first give it a little time, then try comparing…
- How to Delete a Line in Excel: Rows, Cells, and DataTo delete a line in Excel when you mean a whole row, click its numbered row heading, right-click,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.