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.

Image by Author

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

Append Queries

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.

Import data to Power BI (Image by Author)

In the left pane, you will be able to see the imported files.

Query Editor page (Image by Author)

Click on Append files from the top pane. Choose the table to append from the dropdown.

Appending tables (Image by Author)

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.

After appending tables (Image by Author)

Sometimes Power BI takes time to update queries. So, refresh it by clicking on ‘Load more’.

Appended Column values (Image by Author)

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.

Import folder into Power BI (Image by Author)

You will see a preview window with file details. Choose Combine & Transform Data option for importing.

Preview Import window (Image by Author)

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.

Combined files on Importing a folder (Image by Author)

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).