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

Introduction

 
In my previous article,
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.
 
Combine and transform data of multiple files located in a Folder in Power BI
See below the structure of each file,
 
Employee1
 
Combine and transform data of multiple files located in a Folder in Power BI 
 
Employee2
 
Combine and transform data of multiple files located in a Folder in Power BI
 
Employee3
 
Combine and transform data of multiple files located in a Folder in Power BI
 
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’.
 
Combine and transform data of multiple files located in a Folder in Power BI
 
Paste the path of the folder and click ok
 
Combine and transform data of multiple files located in a Folder in Power BI
 
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.
 
Combine and transform data of multiple files located in a Folder in Power BI
 
Click on highlighted text i.e. ‘Combine and Transform’.
 
Step 2
 
Combine and transform data of multiple files located in a Folder in Power BI
 
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.
 
Combine and transform data of multiple files located in a Folder in Power BI
 
Here you can see that Power BI added one additional column that specifies from which file is the specific row in the table.
 
Combine and transform data of multiple files located in a Folder in Power BI
 
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.
 
Combine and transform data of multiple files located in a Folder in Power BI
 
Combine and transform data of multiple files located in a Folder in Power BI
 
Step 3
 
Now go to file tab and click on ‘Close & Apply’. You can perform multiple tasks in power query editor and apply changes.
 
Combine and transform data of multiple files located in a Folder in Power BI
 
Your changes will be saved and shown on Power BI desktop window.
 
Combine and transform data of multiple files located in a Folder in Power BI
 
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’.
 
Combine and transform data of multiple files located in a Folder in Power BI
 
Second report is showing the total Salary of all countries. To do so I am using card visualization with Sum of salary.
 
Combine and transform data of multiple files located in a Folder in Power BI
 

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.