Loading Multiple Files From A Folder Into Power BI
There are countless ways to load data into Power BI.
When you’re learning Power BI, this trick for loading multiple files from a folder at the same time should be near the top of your list – it can save lots of time.
Read on to find out just how you can simplify this process for yourself.
Why Load Multiple Files From The Same Folder Into Power BI?
Imagine the following scenario:
- You have been asked to produce a dashboard using external data, for example, sales data or log files.
- Your vendor does not offer an API or any way to connect to the data source directly.
- Instead, they have agreed to send you flat files regularly with daily or weekly data to create your report.
Does this issue sound familiar?
Most analysts have run into some variation of this problem in their careers.
Whether your organisation lacks a database process for this kind of work, or you just prefer doing things the solution, Power BI has you covered.
As long as the files are identical, you can batch import them from a folder location.
How To Load Multiple Files From A Folder In Power BI?
Experienced analysts can do this in under a minute!
Select “Get data” from the top toolbar.
Under the options, select “Folder” and click “Connect”.
Enter the path of the folder where you have saved your files.
Your files will show up and give you three options.
Load Data, Transform Data and Combine Data.
Click on “Combine”, and two options will appear:
Combine & Transform Data
Combine & Load.
If your folder contains only the files you want and nothing else, you can go ahead and select Combine & Load.
If you need to filter out files or do any other transformation, you need to select Combine & Transform.
In this example, we will be selecting Combine & Load.
A pop-up will ask you to review the combined files.
It will show you sample data and ask you to give it the specifications for each file, for example, the delimiter type.
You will just select “OK” at the bottom in most cases.
Just like that, you will have all of your files combined and can start to create your visuals.
Why Use Power Query To Filter Out Files?
The last section showed a simple example where all your files are the same, and you can just combine them.
In some cases, you will have to filter some files out.
Power query editor allows you to transform your data as you would in other circumstances.
We want to combine the log files and ignore the .lock and .ini files in this example.
You can remove the files that shouldn’t be in your data by filtering out a column.
This will allow you to combine the files. As you can see, we have not ticked the .ini and .lock files, so Power BI will not upload them.
You may need to create a custom column to achieve this in even more complicated examples.
Troubleshooting
1) Combining your data gives you an error or does not give you correct information.
Solution: Make sure your files are identical. Different numbers of columns, different column names or column types will all give you errors.
2) The Combine option is not available when connecting to data.
Solution: The combine option will only be available if you select the “Folder” connector in the Get Data menu.
Final Thoughts
The Combine Files feature in Power BI is a powerful option to combine different files quickly.
It is a simple process that allows you to visualise data (whether a report or a dashboard) across multiple files.
While it will not be ideal in all scenarios, it is a fantastic choice when dealing with multiple identical files.
I you would like to know more about Power BI, try our article about Power BI relationships.
