Introduction
Using Power Query, you can batch import, transform, and aggregate multiple files located in the same folder. In this article, I have summarized the steps on how to actually specify a folder as a data source in Power Query based on what I have learned.
What I Want to Achieve
- Specify a folder and batch import the files inside (such as CSV or Excel).
- Process data in Power Query and output it as a single aggregated dataset.
Steps to Specify a Folder as a Data Source
Follow these steps to specify a folder from Excel and integrate files using Power Query.
1. Open Excel
First, launch Excel as usual.
2. Select “From Folder” from the “Data” Tab
Click the “Data” tab at the top and proceed with the following steps:
- Click “Get Data”.
- Select “From File” -> “From Folder”.
- Select the folder you want to use as data and click “Open”.
3. Select Combine and Transform Files
Next, configure the settings to handle the files in the folder with Power Query.
- Select the “Combine & Transform Data” (or “Combine”) option.
- The “Combine Files” window will appear.
- If there are no problems, click “OK”.
4. Process Data in Power Query Editor
At this point, the “Power Query Editor” will launch.
- Clicking “Transform Sample File” in the sidebar opens one file that serves as a template.
- Perform column deletion, data type conversion, filtering, etc., as needed.
- The transformations will be applied commonly to all files.
5. Load After Processing is Complete
When processing is finished, load the data using the following flow:
- Click “Close & Load” in the top menu.
- Selecting “Close & Load To…” displays the “Import Data” screen.
- Specify the output destination (Table / PivotTable, etc.) and sheet, then click “OK”.
Now, the data retrieved from multiple files in the folder will be displayed in Excel in a state integrated as a single table.
Supplement: What Can Be Done with Sample File Processing?
In “Transform Sample File,” the following data processing is possible:
- Deletion of unnecessary columns
- Data type conversion for numbers and dates
- Extraction of specific characters or splitting by delimiters
- Row filtering and sorting
- Addition of columns and conditional transformations
Since these processes are applied in bulk to all files in the folder, it can significantly reduce effort.
Summary
By specifying a folder as a data source in Power Query, batch loading and integration processing of multiple files can be performed very smoothly.
- Batch load multiple files simultaneously by getting data “From Folder”.
- Create transformation processes for a single file in “Transform Sample File” and apply them to the whole.
- Output processed data to Excel as a table with “Close & Load”.
