Power BI allows users to read multiple files from a folder, making it easier to import and consolidate data from multiple sources automatically. This feature is useful when working with monthly reports, log files, or multiple CSV, Excel, or text files stored in a single directory. By connecting to a folder, Power BI can combine all the files into a single dataset for analysis.
Reading Files from a Folder in Power BI
The Folder Connector in Power BI enables users to load multiple files from a specified directory. It allows for automatic data consolidation, batch processing, and dynamic file updates whenever new files are added to the folder.
Steps to Read Files from a Folder in Power BI
To connect Power BI to a folder and import files, follow these steps:
- Open Power BI Desktop.
- Click on Home and select Get Data.
- Choose Folder from the available data sources.
- Click Connect and browse to the folder location.
- Power BI will list all the files in the folder. Click Transform Data to process them.
- Use Power Query to clean, filter, and combine the data.
- Click Close & Apply to load the final dataset into Power BI.
This process enables Power BI to automatically detect, import, and combine files stored in the selected folder.
File Types Supported in Folder Import
Power BI can load multiple files from a folder, including:- Excel files (.xlsx, .xls, .xlsm)
- CSV files (.csv)
- Text files (.txt, .tsv)
- JSON files (.json)
- XML files (.xml)
- Parquet files (.parquet)
Combining Files from a Folder
After connecting to a folder, Power BI provides an option to combine multiple files into a single dataset.
To combine files:
- In Power Query, click Combine Files.
- Select a sample file for formatting.
- Define the header row and data structure.
- Power BI will apply the same structure to all files.
- Click OK to merge the files into a single dataset.
This method is useful for consolidating monthly sales reports, customer logs, or transaction data.
Automating Data Updates
If new files are added to the folder, Power BI can automatically detect and include them in the dataset.
To enable automatic updates:
- Ensure all files follow the same naming conventions and structure.
- Set up a scheduled refresh in Power BI Service for real-time updates.
- Use folder monitoring to track new file additions.
Power BI will automatically load new files without requiring manual intervention.
Best Practices for Folder-Based File Import
- Keep all files in the folder consistent in format and structure.
- Rename or archive old files to avoid duplicate records.
- Use Power Query transformations to clean and structure the data.
- Apply filters to exclude unnecessary files or subfolders.
- Use relative folder paths for dynamic folder selection.
Following these practices ensures clean, structured, and automated data consolidation.
Conclusion
Reading files from a folder in Power BI is an efficient way to import, consolidate, and automate data processing. By using the Folder Connector, businesses can easily merge multiple files, enabling seamless reporting and analysis. With scheduled refresh and proper data transformations, Power BI ensures real-time updates and data accuracy for decision-making.
