EClytics: Business Intelligence Services
How Can We Help?
< All Topics
Print

It is effortless to Append and Merge in Power BI. Learn How!

What is append query in Power BI and how to use it?

As we know, Power BI is an extraordinary tool for your data problems and data visualizations. We can append and merge in Power BI very easily. In this blog, you will learn more about these in detail. Let us learn together!

Append Query is used to join two or more tables vertically.

For example, if we have two tables, out of which one is having 100 rows and other one is having 50 rows, then appending the two tables will generate a single table that will be having total of 150 rows.

Append tables having same column names

  • Let us consider two tables having sales of two different years and we want to append the two.

Follow the path:

Get Data> Excel > Load and Transform > Append queries command on the Home tab in the Combine group. Here, there are two options: Append queries or Append queries as new (as shown below):

  1. To keep the existing query result as it is, choose Append queries
  2. To create a new query with the appended result, choose Append Queries as New. Click OK.

Now, you can see, a new query will be created with all your tables appended as in the figure.

Append will not remove the duplicate from the result. For that, use Remove Duplicates in the Power Query Editor.

Append Tables having different column names

Let us consider appending two tables having different column names but number of columns is same:

After we import the data and append as new in the Power Query Editor as we have done above, we get the following result:

Here, the result shows that all the columns from both tables will be created in the final table. The rows will be null for the columns which are not there in their respective tables.

Append Tables having different number of columns

Let us consider two tables having different column names as well as different number of columns.

After we import the data and append as new in the power query editor as we have done above, we get following result:

I hope you are quite clear with the append query, so now let us learn the merge query.

What is Merge query in Power BI and how to use it?

Merge Query joins the two tables based on matching values from one or multiple columns horizontally.

Depending upon the requirement, different types of joins are available:

Let’s consider merging two tables:

Go to Get Data> Excel> Load>Transform Data > Home tab in power query editor. After this select  > Merge queries or Merge queries as new.

Two options are given:

  1. Merge queries: This option is to keep the query as it is and,
  2. Merge queries as new: This option is to create a new query. Once we choose Merge as new, we will next identify the tables for merging as left and right. By default, the merge will show Left Outer join, so first table selected will be the left table and second table will be the right table. Click ok.

Then, the tables will be merged and as a result, the resultant table will be shown as follows:

Here, all the values from the left table and all the matching rows from both tables will be visible. There are six types of joins in Power BI and they can be chosen according to the requirement of the report:

Fuzzy Matching is the fuzzy matching algorithms that are compared when matching the columns of the tables. “Use fuzzy matching to perform the merge” is to be selected for this method.

We hope you enjoyed reading and found this helpful! You can also check out few more articles on Power BI from our blog.

Add comment

Table of Contents