In Power BI, the Power Query Editor is a powerful tool that enables you to transform and shape your data to fit your needs. This post will cover some essential table transformations and operations you can perform in the Power Query Editor, including merging and appending tables, disabling the load of a table, referencing vs. duplicating queries, and showing query dependencies.

Appending and Merging Tables

Appending Tables

Appending is useful when you want to stack rows from two or more tables with the same structure into a single table.

• Open Power Query Editor: Click on Transform Data.
• Open the Append options: Go to Home > Append Queries.
• Choose Tables: Select the tables you want to append.
• Confirm Append: Click OK and the tables will be combined.

In the example above, we have two tables containing flight data as a basis. flights_S1 contains the flights from the first half of the year and flights_S2 the flights from the second half of the year. To obtain a table with all flights, we need to append the two tables. Here we have selected the option “append queries as new” , so that we get a new table (“flights_all”). This option is visible by clicking on the down arrow next to “Append Queries”. We could also select the option “three or more tables” if we want to append more than 2 tables.

When to Use: Use appending when you have multiple tables with the same columns and you want to create a single table that includes all rows.

Merging Tables

Merging is used when you need to combine columns from two or more tables based on a common key. Here’s how you can do it:

• Open Power Query Editor: Click on Transform Data from the Home tab.
• Select the Tables to Merge: Go to Home > Merge Queries.
• Choose the Tables and Columns: Select the tables and the columns you want to merge on.
• Configure Join Type: Choose the type of join (Inner, Outer, Left, Right, etc.).
• Expand Merged Columns: After merging, expand the merged table to include the desired columns.

In the example shown we are performing a left outer join on the “flights_all” table with the airlines table on the columns “AIRLINE” (flights_all) and “IATA_CODE” (airlines).

When to Use: Use merging when you need to bring additional data from another table based on a relationship.

Disable the Load of a Table

Sometimes, you might have intermediary tables that you don’t want to load into the Power BI model. In our case, for example, these would be the “flights_S1” and “flights_S2” tables. Since we have combined these two tables and created the new “flights_all” table, we no longer need to load the data from these two tables into our data model. Here’s how to disable the load:

• Open Power Query Editor.
• Right-click on the Table: In the Queries pane, right-click on the table.
• Disable Load: Select Enable Load to toggle off the loading of the table.

This can help in optimizing your model and reducing unnecessary data load.

Referencing vs. Duplicating Queries

Referencing a Query

Referencing a query creates a new query that points to the original query. Changes to the original query will affect the referenced query.

• Right-click on the Query: In the Queries pane, right-click on the query you want to reference.
• Select Reference: Choose Reference.

Advantages:

• Keeps your model dynamic as changes propagate.
• More efficient memory usage as it avoids duplicating data.

Duplicating a Query

Duplicating a query creates a new query that is an exact copy of the original, but independent of it. Changes made to the original Query after it was duplicated will not affect the duplicate of the Query.

• Right-click on the Query: Right-click on the query you want to duplicate.
• Select Duplicate: Choose Duplicate

Advantages:

• Allows for different transformations without affecting the original query.
• Useful when you need a snapshot of the data at a certain point.

Showing Query Dependencies

Understanding how queries are related can be crucial, especially in complex models. Power Query Editor allows you to visualize these dependencies. Here’s how you can do it:

• Open Power Query Editor.
• Go to View Tab: Click on View.
• Select Query Dependencies: Click on Query Dependencies.

The image above shows the query dependencies after we applied the steps described previously. We can see, that “flights_S1” and “flights_S2” are no longer being loaded and combined in the table “flights_all”. Additionally we see that the “airlines” table is joined with the “flights_all” table.

Incorporating these techniques into your Power BI workflow will enhance your data transformation capabilities, making your data models more efficient and easier to manage. Stay tuned for more tips and insights on working with Power BI!