Power BI

Append vs. Merge in Power BI | Power BI training in Australia

Data transformation is an essential topic of both basic and advanced Power BI trainings because data must be transformed to a format that is suitable for fast and precise data processing and for efficient reporting.  Data Transformation involves a wide range of operations such as identifying data sources and data types, cleaning data by removing errors and duplicates along with enriching data and performing aggregations.

Here’s a blog to guide you through the basic data transformation principles: https://powerbitraining.com.au/using-group-by-in-power-bi-for-data-transformation/

The first half of this blog enlists the data transformation principles and the second half performs basic data transformation using the group by method.

Data transformation consists of series of steps known as queries. Few common queries used in data transformation are:

  • Duplicate
  • Reference
  • Append
  • Merge

Duplicate and reference queries have already been discussing in our blogs and now we focus on append and merge queries.

To learn duplicate and reference visit: https://powerbitraining.com.au/duplicate-vs-reference-queries/

Appending Queries

Appending Queries is a feature in Power BI that allows you to create a stacked table out of similar data tables. It attaches one table to another in a top-down manner. Appending queries typically increases the row count of tables.

To Append a Query:

  1. Click on Berlin in the Queries Pane.

2. Click on Chicago in the Queries Pane.

3.Click on Berlin and then Click on Home Tab in the Ribbon Menu.

4.Click on Append in the Combine

5.Click on Append as New.

A pop-up appears.

6. From the drop-down under the Primary table, select “Berlin”.

7. From the drop-down under Table to append to the “Primary Table, select “Chicago”.

8. Click OK.

Berlin contained three columns whereas Chicago contained four columns. Three columns from Chicago were like Berlin so the data in those columns was simply appended to the rows of Berlin. The Column Retailer Type did not exist in Berlin dataset. Therefore, it contains null values where the RetCity is Berlin.

Merging Queries

Merging Queries allows you to merge data sets that contain a common column. Merge query adds columns to the table. Merge query work with dissimilar data sets linked by foreign and primary key.

To Merge Queries:

1. Click on Sales.

2. Click on Sales Table. Click on Home Tab in the Ribbon Menu.

3. Click on Merge in the Combine section.

4. Click on Merge as New.

A pop-up menu appears.

5. From the first drop-down menu, select Sales and click on RetCity

6. From the second drop-down menu, select Countries and click on City.

7. Click OK.

8. Open Merge Query and click on the double headed arrow.

A pop-menu appears which allows you to choose column of your choice.

9. Click OK.

Power BI provides you with six types of merging options. Here’s a blog about the different merging options available in Power BI: https://powerbitraining.com.au/merge-options-in-power-bi/

Append vs. Merge Query:

Append and merge perform different actions on the raw data. Append refers to stacking of rows of the data. The kind of tables used in append query are slightly similar if not exactly similar. This means append query increases the row count of structurally similar data. Merge query on the other hand is to join tables with a common column. The name of the column can be different but the column must hold similar values to define the merge. Merge query therefore increases the column count.

Conclusion:

Data transformation refers to the process of performing appropriate actions to convert raw data into useful information. The transformed data makes data analysis easier and uncovers useful insight. Data transformation consists of series of steps known as queries. In this blog post, the append and merge queries have been discussed.

Are you a data analyst and want to learn more about Power BI? Why not sign up for Power BI training in Australia. We provide our services in the following regions

Back to list

Leave a Reply

Your email address will not be published. Required fields are marked *