In this episode of the Power Query series, Allison Gonzalez, a Microsoft certified trainer at Pragmatic Works, explains the process of merging data sources in Power Query, both in Power BI and Excel. The focus is on bringing together two different data sources using a merge to combine data and add new columns. Allison goes over the process step-by-step and compares how this works in both Power BI and Excel.
What is a Merge in Power Query?
A merge in Power Query is the process of combining data from two separate tables by adding columns from one table to another. Unlike appending data (which adds rows), merging adds columns based on a shared column (often referred to as the key column) between the two tables. This operation ensures that the row structure is aligned, and the data matches up based on the chosen key.
Key Considerations for Merging
- Key Column: To perform a merge, at least one column must match between both tables. The content in this column needs to be the same to align the data correctly.
- Join Type: Power Query offers various join types, such as Left Outer, Right Outer, Full Outer, Inner, Left Anti, and Right Anti joins. The choice of join type determines how data is combined and which rows are included in the final result.
Types of Joins in Power Query
Understanding the different types of joins is essential for merging data correctly. Below are the most common join types:
- Left Outer Join: Returns all rows from the first table and matching rows from the second table.
- Right Outer Join: Returns all rows from the second table and matching rows from the first table.
- Full Outer Join: Returns all rows from both tables, matching where possible.
- Inner Join: Returns only the rows that match between the two tables.
- Left Anti Join: Returns rows from the first table that have no match in the second table.
- Right Anti Join: Returns rows from the second table that have no match in the first table.
Step-by-Step Guide to Merging Data in Power Query
Allison walks through a practical example where she combines two data sources: income and population data for various countries across different years. Here's a breakdown of the steps she followed:
- Prepare the Data: Ensure that the data is clean and that the columns are properly labeled. Allison emphasizes the importance of renaming columns for clarity.
- Select the Tables: In the Power Query editor, select the two tables you want to merge. In this example, the tables are income and population data.
- Choose the Key Columns: Select the matching columns from both tables (e.g., country and year) that will serve as the key for the merge.
- Choose the Join Type: Allison selects the Inner Join, ensuring that only matching rows from both tables are included in the final result.
- Perform the Merge: Use the "Merge as New" option to create a new table combining the selected columns from both data sources.
- Expand the Columns: Once the merge is complete, expand the new table to include only the necessary columns, such as the population column in this case.
Working in Excel vs. Power BI
Allison also demonstrates how to perform the same merge operation in Power Query within Excel. The process is very similar to that in Power BI, with only slight differences in the interface and options for loading the data. In Excel, you can choose to load the merged data into a table for further analysis or keep it as a connection to the source data.
Tips for Using Power Query
- Before merging, ensure that your key columns are correctly matched and formatted in both tables.
- Use the "Merge as New" option to avoid overwriting the original tables, keeping your data history intact.
- If you have multiple sources, it's important to carefully select the correct join type to match your desired outcome.
In summary, merging in Power Query allows users to combine data from different tables based on shared columns, providing a more organized and unified dataset. By following these steps and understanding the different join types, users can perform efficient data merges in both Power BI and Excel.
If you want to learn more about Power Query and data transformations, check out the rest of the episodes in Allison Gonzalez's Power Query series at Pragmatic Works.
Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Excel and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.