Power BI: Combining data from different sources
Power BI is a Data Visualization product developed by Microsoft. It can get data from different sources, which can be combined and cleaned. The transformed data can be modeled and visualized using a variety of Visualization tools. These interactive visualizations can be shared with stakeholders in the form of reports or dashboards.
Normally, we would have to use data from multiple data sources to come up with a final visualization. In many scenarios, we might have to append several files into one or combine files to arrive at a result. This can be achieved in two ways.
For learning purposes, let’s say that we have to use all the files in a folder to come up with a report. The first method is to import each file into Power BI and append the tables
Import the files separately by selecting the type of file that you are going to use. In this example, we will be using CSV files. Once you have imported it, don’t directly load the files. Instead, click on Transform while importing.
In the left pane, you will be able to see the imported files.
Click on Append files from the top pane. Choose the table to append from the dropdown.
You will notice a new step ‘Appended Query’ added to the Applied Steps on the right pane. You can check if the tables have been appended by checking the column values. Here, we can see the column ‘Country’ has only Germany. But, there must be Germany and Canada after appending.
Sometimes Power BI takes time to update queries. So, refresh it by clicking on ‘Load more’.
Now you can see that there are two values for column ‘Country’.
Combine Files from a Folder
Import a folder with data files into Power BI.
You will see a preview window with file details. Choose Combine & Transform Data option for importing.
The Power Query Editor displays the combined data from all the files in the folder. You can see that it has automatically added some steps to the Applied Steps section while combining.
Click on the Country column to see the values from every table.
While Combining files or Appending Queries, it is important to note that every dataset has the same schema (columns and datatypes).