When there are multiple files to import there is an easy way to import these files at once to Power BI Desktop Application. You should utilize the Folder import method of Get Data functionality.
To do this just follow “File > Get Data > More” and select Folder on right side or just write Folder to search box on top left corner.
You will be able to select, load and combine data from different files together and create reports. This is especially useful when an automation/application creates a text/csv output to a folder regularly and you have to combine all data to conduct an analysis. This functionality saves you from painful and lofty ETL process just to load the data to a database and consume it again to create a report. Power BI Desktop Folder import functionality is a life saver.
Scenario 1: Multiple files categorized/aggregated per entity such as Country.
In this scenario assume that our automation exports sales data to a file server folder grouped by Country. So that we have a seperate file for each country.
Now we will select this folder and let Power BI Desktop Folder import functionality to do its best. Just follow “File > Get Data > More” and select or find “Folder” in right section. Click “Connect”
Power BI Desktop will open Folder Browser Dialog. Navigate to the folder where your csv files were exported and click OK.
Power BI Desktop will display an intermediate window which summarizes the content of the selected folder. You can also see file properties of each content.
On bottom of this screen there are three main buttons and five functionalities:
Load: Loads the file structure (without combining all files to one file) to Power BI Desktop but will not import the actual data.
Transform Data: Loads the file structure to Power BI Desktop and opens Power Query Editor to edit/transform it.
Combine & Load: Combine all data and load into model.
Combine & Transform Data: Combine all data and load into model for editing.
Let us continue with Combine and Transform Data. This will open a new window for us to configure combination options for those files we’ve selected.
Let us take the first file as a structure (it would not be a problem when all our files have the same structure) and continue. Power BI Desktop will open Power Query Editor (because we’ve selected Transform) for our customization requests. After we’ve done with the customizations we can simply click on Close & Apply (or just Apply) in Power Query Editor to proceed with data load.
There could be errors raised during this loading process. If any errors occured it will be reported on the same dialog.
When you click on “View Errors” you will be redirected to list of errors where you can check and see the details of those errors.
If everything goes well, you will be able to see this data in Power BI Model.