Appending QueriesAppending 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:
- 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 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.
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
- Power BI Training in Sydney
- Power BI Training in Melbourne
- Power BI Training in Brisbane
- Power BI Training in Canberra