Combine And Transform Data Of Multiple Files Located In A Folder In Power BI

Introduction

 

In my previous article,

• How To Import Data From Folder Into Power BI
  

I have shown you how to get data from multiple files located in a folder. If you went through this article you know that we have two options to combine the files.

• Combine and Transform
• Combine and Load

In my previous article we have used ‘Combine and Load’. In this article we will see how to use ‘Combine and Transform’. To do this I am going to use the same tables that I used in previous articles. Have a look at the  files.

 

See below the structure of each file,

 

Employee1

 

 

 

Employee2

 

 

Employee3

 

 

Follow the below steps to combine and transform the data.

 

Step 1

 

To import data, go to ‘Home’ tab click on ‘Get data’ select the ‘Folder’ source then click ‘Connect’.

 

 

Paste the path of the folder and click ok

 

 

After clicking on ‘Ok’ you will see what is located in this folder. You can see the meta data of the files like name, type, when it last modified etc.

 

 

Click on highlighted text i.e. ‘Combine and Transform’.

 

Step 2

 

 

Choose the first file to combine and click on ‘Ok’. Now Power query editor will be opened and you can see that all three tables are combined.

 

 

Here you can see that Power BI added one additional column that specifies from which file is the specific row in the table.

 

 

This column is very important, and we can use it like every other column to slice data based on its values (In this case its OFC_LOC). But in this case, we already have a Country in OFC_LOC column, therefore we do not need it and we will remove it. By having the column selected, we click Remove Columns.

 

 

 

Step 3

 

Now go to file tab and click on ‘Close & Apply’. You can perform multiple tasks in power query editor and apply changes.

 

 

Your changes will be saved and shown on Power BI desktop window.

 

 

Step 4

 

Now you can create a report from this table. Here I am using Pie chart and two columns i.e. ‘Ofc_loc’ and ‘Salary’.

 

 

Second report is showing the total Salary of all countries. To do so I am using card visualization with Sum of salary.

 

 

Summary

 

Try these above steps, you will be able to create a combine table by using power query. You can transform the data as per your need in power query editor. I will write a separate article on Power Query Editor so continue with me. Thanks for reading.