Combining queries is a potent tool, enabling the merging or appending of diverse tables or queries. Consolidating tables into one is beneficial when:
The presence of numerous tables complicates navigation within a semantic model.
Multiple tables serve similar roles.
A table possesses only one or two columns suitable for inclusion in a different table.
You intend to utilize various columns from distinct tables in a custom column.
The merging and appending methods offer flexibility in combining tables.
In the process of crafting Power BI reports for the Sales and HR departments, the task involves generating a comprehensive contact information report. This report should encompass details such as contact information and location for every individual associated with the company, including employees, suppliers, and customers. The relevant data is distributed across the HR.Employees, Production.Suppliers, and Sales.Customers tables, as illustrated in the provided image.
Append queries
The process of appending queries involves the addition of rows of data to another table or query. For instance, if you have two tables—one with 300 rows and another with 100 rows—appending queries results in a combined table with 400 rows. On the other hand, merging queries entails adding columns from one table or query to another. To perform a successful merge of two tables, a key column that establishes a connection between the two tables is necessary.
In the mentioned scenario, you'll merge the HR.Employees table with the Production.Suppliers and Sales.Customers tables to create a unified contact information list. To achieve this, it's crucial that the relevant columns in the original data tables have identical names when combining queries, ensuring a consolidated view of the data for employees, suppliers, and customers.
Before initiating the query combination process, you can streamline your tables by removing unnecessary columns. To accomplish this, each table should be formatted to retain only four columns containing essential information, and these columns should be uniformly renamed as follows: ID, company, name, and phone. The provided images depict excerpts from the adjusted Sales.Customers, Production.Suppliers, and HR.Employees tables.
Once the reformatting process is complete, you can merge the queries. Navigate to the Home tab on the Power Query Editor ribbon and access the drop-down list for Append Queries. Here, you have two options: 'Append Queries as New' generates a new query or table as the output of the appending operation, while 'Append Queries' adds rows from an existing table to another.
Your subsequent task involves creating a new master table, requiring the selection of 'Append Queries as New.' This choice leads to a window where you can designate the tables for appending, moving them from 'Available Tables' to 'Tables to Append,' as demonstrated in the accompanying image.
After you have added the tables that you want to append, select OK. You'll be routed to a new query that contains all rows from all three of your tables, as shown in the following image.
You have now succeeded in creating a master table that contains the information for the employees, suppliers, and customers. You can exit Power Query Editor and build any report elements surrounding this master table.
However, if you wanted to merge tables instead of appending the data from one table to another, the process would be different.
Merge queries
Merging queries involves consolidating information from several tables into a unified table, utilizing a shared column, much like the SQL JOIN clause. For instance, if the Sales team requires the amalgamation of orders and their respective details from separate tables (Orders and OrderDetails), you can achieve this by merging these tables based on the common column, OrderID, as depicted in the provided image.
Go to Home on the Power Query Editor ribbon and select the Merge Queries drop-down menu, where you can select Merge Queries as New. This selection will open a new window, where you can choose the tables that you want to merge from the drop-down list, and then select the column that is matching between the tables, which in this case is orderID.
In the merging process, similar to SQL JOIN statements, you have options to determine how the two tables should be joined:
Left Outer Join - Presents all rows from the first table along with only the matching rows from the second.
Full Outer Join - Shows all rows from both tables.
Inner Join - Reveals only the matched rows between the two tables.
For the current situation, you'll opt for a Left Outer Join. After making this selection, click OK, leading you to a new window where you can inspect your merged query.
Now, you can merge two queries or tables in different ways so that you can view your data in the most appropriate way for your business requirements.
SWETA SARANGI
23.02.2024