Two powerful operations, which are merging and appending queries, allow data analysts to amalgamate data from multiple sources and to present it in a single table. In this article, we will discuss these techniques for managing and manipulating data in PL-300 Microsoft Power BI exam context.
1. Merging Queries
Merging queries refers to the process of combining two or more tables by matching data in one (or more) column. This process is similar to JOIN operations in SQL, where you can join tables based on a related column between them.
For instance, imagine two tables, Product (with fields ProductID, ProductName) and Sales (fields ProductID, SalesAmount). If you wanted to combine these two tables based on the shared ‘ProductID’ column and aggregate the SalesAmount for each product, you would merge these queries.
Example:
Let’s illustrate this with a step-by-step example on how to merge queries:
- Start with opening the Power Query Editor. Select the ‘Sales’ table and click on the ‘Merge Queries’ option in the Home tab.
- In the Merge queries pane, choose the second table ‘Product’. Choose a join type according to your needs (For instance, an Inner join return only the matching rows from both tables).
- Select the matching column ‘ProductID’ in both datasets. Click ‘OK’.
- Power BI merges the tables to form a new column that consists of tables corresponding to the matched ‘ProductID’.
- Click on the ‘Expand’ button present in the header of the new column to select the fields you want from the ‘Product’ table (in our case, ‘ProductName’).
- An expanded new table will show the ‘ProductName’ and ‘SalesAmount’ matched on ‘ProductID’.
2. Appending Queries
Appending queries is the process of stacking rows from multiple tables to create a single table. The main condition for append operation is that the tables should have the same columns (or at least, the columns that you want to append need to exist in all tables).
By applying an appending operation, you will stack one table on top of the other, adding the rows from one into the other, just like the UNION ALL operator in SQL.
Example:
Imagine you have two sales tables: ‘Sales2019’ and ‘Sales2020’, both with the same structure (ProductID, SalesAmount), but for a different period. Your task is to combine both into one ‘TotalSales’ table. Here’s how to append these queries:
- Open the Power Query Editor. Go to ‘Home’ > ‘Combine’ > ‘Append’. If you can’t see the Append Queries option, make sure at least one query is selected in the Queries pane.
- In Append Queries pane, you can select the tables you want to append. Select ‘Sales2019’ and ‘Sales2020’. Click ‘OK’.
- Power BI appends the rows from ‘Sales2020’ into ‘Sales2019’, giving you a single table with sales information for the two years.
The main difference between Merge and Append Queries lies in their operations. A merge combines data from rows across multiple tables based on a common attribute, while an append stacks tables on top of each other, combining data from different rows. Both of these operations are necessary for a comprehensive data manipulation and analysis, and are therefore significant aspects of the PL-300 Microsoft Power BI exam.
Practice Test
True/False: You can’t edit the steps of a merge in Power Query.
- True
- False
Answer: False
Explanation: In Power Query, you can edit the steps of a merge by navigating to the Applied Steps section.
Single Select: Which operation do you need to perform to combine rows from two tables into one table in Power Query?
- a) Merge
- b) Append
- c) Join
- d) Filter
Answer: a) Merge
Explanation: Merge operation in Power Query combines rows from two tables based on a related column between them.
True/False: When you append queries, new columns are added to the end of the table.
- True
- False
Answer: False
Explanation: When you append queries, new rows are added to the end of the table, not new columns.
Multiple Select: Which of the following are options for Merge Type in Power Query?
- a) Full Outer
- b) Inner
- c) Left Outer
- d) Right Inner
Answer: a) Full Outer, b) Inner, c) Left Outer
Explanation: Full Outer, Inner, and Left Outer are options for Merge Type in Power Query. Right Inner does not exist in Power BI.
True/False: Merge Queries option in Power BI always adds new columns to your query.
- True
- False
Answer: True
Explanation: When you merge queries in Power Query, it joins tables together and adds new columns to your query.
Single Select: What is another name for APPEND Queries in Power BI?
- a) Combine Queries
- b) Join Queries
- c) Union Queries
- d) Split Queries
Answer: a) Combine Queries
Explanation: APPEND Queries is also known as Combine Queries in Power BI as it combines rows of two or more tables into one table.
True/False: Merge and Append Queries in Power BI maintain data lineage.
- True
- False
Answer: True
Explanation: Merge and Append operations in Power BI maintain data lineage, meaning transformations applied are kept and can be traced back.
Multiple Select: Which of the following are ways you can merge queries in Power Query?
- a) By position
- b) By content
- c) By column values
- d) By table size
Answer: a) By position, b) By content, c) By column values
Explanation: Merging in Power Query can be done by position, content, or column values. There is no option to merge by table size.
True/False: All columns used in Merge operation have to be of the same data type.
- True
- False
Answer: False
Explanation: In Power BI, columns used in Merge operation can have different data types based on the logic needed for the merging.
Single Select: In Power Query, if two tables don’t have any columns in common, which operation should you perform to combine them?
- a) Merge
- b) Append
- c) Join
- d) Filter
Answer: b) Append
Explanation: If two tables don’t have any columns in common, Append operation can be performed to combine the tables by adding rows to one table from the other.
True/False: Append operation in Power Query adds additional columns from another table when there is no matching data.
- True
- False
Answer: False
Explanation: Append operation in Power Query combines rows from different tables into a single table, not columns.
Multiple Select: In which situations would you use Append operation instead of Merge in Power BI?
- a) When you want to consolidate similar data from multiple tables
- b) When you want to connect two tables based on a defined relationship
- c) When your tables share the same columns but different rows
- d) When you need to add new calculated columns in a table
Answer: a) When you want to consolidate similar data from multiple tables, c) When your tables share the same columns but different rows
Explanation: Append operation is helpful when you need to consolidate similar data from different tables or when your tables share similar columns but different rows.
Interview Questions
What does it mean to merge queries in Power BI?
Merging queries in Power BI involves combining rows from two or more tables in a way that is similar to using JOINs in SQL. In this case, multiple tables are consolidated based on a related column between them.
What is the primary purpose of appending queries in Power BI?
The append queries function in Power BI is used to stack rows from multiple tables, joining queries vertically. It’s similar to executing a UNION ALL operation in SQL. It is mainly used when the tables have similar structures and you want to consolidate them into one.
What happens when you merge two queries that do not have a matching column?
If you try to merge two queries that don’t have a matching column, Power BI will not be able to complete the operation. Merged queries require at least one column to match or relate.
When should you use a Full Outer join in Power BI?
You should use a Full Outer join when you want to keep all rows from both tables during a merge operation. Any non-matching values are filled with NULLs.
Can you append queries that do not have the same structure or columns?
Yes, you can, but the unduplicated columns will be filled with NULL, which might not be desirable for all datasets.
How do you merge queries in Power BI?
You merge queries in Power BI by selecting the ‘Merge Queries’ command in the Home tab within the Query Editor window. Then, you choose the type of join from the Join Kind dropdown (like Full Outer, Right Outer, Inner, Left Anti), select common keys, and click OK.
How can you handle mismatch errors when merging queries?
You can handle mismatch errors when merging queries by carefully checking the data types and content of the columns you are merging. Sorting or removing duplicates may also help to resolve the issue.
Can you merge or append more than two tables in Power BI?
Yes, you can merge or append more than two tables in Power BI. You can select multiple tables while appending, but merging typically happens two tables at a time.
Which join type only shows the rows where there’s a match in both tables?
Inner Join only keeps the common rows where there’s a match in both tables.
What needs to be the same for two queries to be merged in Power BI?
To merge two queries in Power BI, they need to have at least one column in common. The columns being merged should ideally have the same data type and similar content.
What type of join keeps only the rows of the first (or top) table and matching rows from the second table?
A Left Outer join keeps only the rows of the first (or top) table and the matching rows from the second table.
How should the datasets be structured when you use the Append feature?
When using the Append feature, it’s best to work with datasets structured in a similar way or with at least some common columns.
What is the result of appending queries in Power BI?
Appending queries in Power BI results in a new query which combines all the rows from the input queries. It effectively stacks the queries vertically into one comprehensive table.
In Power BI, what does setting the join kind to Right Anti do in the merge queries process?
Setting the join kind to Right Anti in Power BI’s merge queries process keeps only the rows from the second (or bottom) table that don’t match with rows in the first (or top) table.
Can we perform the merge and append operations on queries using Power BI Desktop?
Yes, both merge and append operations on queries can be performed using the Power BI Desktop.
extutorials.com