How to Load Data from a Folder in Power BI

One powerful Power BI feature is the ability to import data from multiple files located in a folder. To be able to do this, the files must have the same schema (same number of columns, same datatype, etc.). In this article, we will describe this in more detail.

Let suppose we have four sales files, each representing sales generated in a different country:
Power Bi Sales Generated Files

Let’s see the structure of each of these files:
Power Bi File 1

Power Bi File 2 Power Bi File 3 Power Bi File 4

We can see that the structure is the same and that the main difference between the files is that the sales are generated in four different countries (Canada, Germany, France and Mexico).

Now we will load these files into the Power BI Desktop.

First, we choose the Get Data > More option in the Power BI Desktop:
Power Bi Get Data Option

This will open the Get Data dialog:
Power Bi Get Dialog Option

We click Folder and then Connect. A new dialog box opens and asks us to provide the folder path:
Power Bi Folder Path

After selecting the file path, we click OK:
Files Ok Successful

We can see that the files were read successfully. Now, we can choose to Combine and directly load the files or to Combine and Edit, which takes us to the Power Query Editor:

Power Bi Combine

After this, we can see a Preview of the combined data:
Power Bi Combine Preview

We click OK and we go to the Power Query Editor. Here we can see how Power BI combined the files into one single table:
Power Query Editor Table

It is important to note that in addition to the original columns from files, Power BI added a new column that specifies which row corresponds to which file in the table:
Power Bi New Column

This column is very important, and we can use it the same as every other column to slice data based on its values (in this case the value is Country). In our example, we already have the Country column, so we don’t need it and we will remove it. We select the column, then click Remove Columns:
Power Bi Remove Columns

Now, we can explore data by creating a table visual that contains the CountryUnits and Revenue   columns:
Power Bi Table Visual

We can see that the values for Units and Revenue are summarized and these represent the sum of Units and Revenue columns in each of our files.

If we added two more files in our folder –  one for sales in the USA and one for Sales in the UK, we would simply see two more rows added to it – one for Units and Revenues in the USA, and the other one for Units and Revenues in the UK.

The features available in Power BI are extensive and becoming an expert takes time, effort and some experimentation. We’re always looking for new ways to use the technology we have to improve the day to day lives of ourselves and our customers. Learn more about how Jet Reports gives you an easy way to access and organize the data in Power BI in this 1-minute video. Contact us to ask about Power BI!

Get the Full Gartner Magic Quadrandt for BI Report

Get the full report on the top BI Vendors.

Get the Report

Get the Full Gartner Magic Quadrandt for BI Report

Get the Report