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:

Let’s see the structure of each of these files:

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:

This will open the Get Data dialog:

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

After selecting the file path, we click OK:

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:

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

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:

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:

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:

Now, we can explore data by creating a table visual that contains the Country, Units and Revenue   columns:

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!