We have two options to combine the files.
- Combine and Transform
- Combine and Load
In this article, we will see how to use ‘Combine and Transform’. 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.
Ref: https://www.c-sharpcorner.com/article/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi/