Power Query in Excel and Power BI provides robust data transformation capabilities. One such feature is “Merge”, which allows you to combine data from multiple tables based on common keys. Understand the intricacies and best practices of merging data in this workout.
Scenario:
You’re an analyst at a logistics company. You’ve been provided with two tables in Power Query: one contains shipment IDs and product details, while the other lists shipment IDs and delivery dates. Your task is to merge these tables to get a comprehensive view of each shipment.
Objectives:
By the end of this workout, you should be able to:
Understand the various merge types in Power Query.
Combine data from multiple tables efficiently.
Troubleshoot common issues that arise during the merge process.
Interactive Task:
Given the aforementioned tables and the following tasks, describe your approach in Power Query:
Create a new table that has all the shipment details (from both tables) for each shipment ID.
Your Approach: ________________________
Generate a table that includes only the shipments present in both tables.
Your Approach: ________________________
Form a table that displays shipments from the first table, even if they don’t have a matching ID in the second table.
Your Approach: ________________________
Questions:
In the context of Power Query, what does an “Inner Merge” result in?
i) A table with rows that have matching keys in both tables.
ii) A table that combines all rows from both tables.
iii) A table with rows from the first table, regardless of whether they have a match in the second table.
iv) A table with unmatched rows from both tables.
If you want to ensure that all data from both tables is retained in the merged table, which merge type should you use?
i) Inner Merge
ii) Left Outer Merge
iii) Right Outer Merge
iv) Full Outer Merge
Duration: 25 minutes
Difficulty: Intermediate
Period:
This workout will be released on Tuesday, September 5, 2023, and will end on Thursday, September 28, 2023. But you can always come back to any of the workouts and solve them.
Create a new table that has all the shipment details (from both tables) for each shipment ID.
Your Approach:
Sure, here’s a step-by-step approach to combine the two tables in Power Query:
Load the tables into Power Query: In Excel, go to the “Data” tab, select “Get Data”, then “From Other Sources”, and finally “From Table/Range”. Do this for both tables.
Rename the tables (optional): To make it easier to identify the tables later, you can rename them. To do this, go to the “Query Settings” pane on the right, click on “Name”, and type in a new name.
Merge the tables: Go to the “Home” tab, select “Combine”, and then “Merge”. A dialog box will appear. Here, you’ll need to select the two tables you want to merge (the shipment details table and the delivery dates table).
Select the join kind: In the same dialog box, you’ll need to choose a join kind. Since you want all shipment details for each shipment ID, you should select “Full Outer” (all rows from both tables).
Select the matching columns: Still in the dialog box, click on the column that contains the shipment IDs in both tables (these are your matching columns). Then click “OK”.
Expand the new table: After merging, your query will now include a new column that contains a table for each row. To expand this table and see all of its data, click on the double-arrow icon in this column’s header. A dialog box will appear allowing you to select which columns you want to include in your final table.
Load the new table into Excel: Once you’re done with all these steps, go to the “Home” tab, select “Close & Load”, and your new table will appear in Excel.
Remember that Power Query is case sensitive, so make sure your shipment IDs match exactly in both tables. If they don’t, you might need to clean your data before merging.
Generate a table that includes only the shipments present in both tables.
Your Approach:
Sure, here’s a step-by-step approach to create a new table that includes only the shipments present in both tables using Power Query:
Load the tables into Power Query: In Excel, go to the “Data” tab, select “Get Data”, then “From Other Sources”, and finally “From Table/Range”. Do this for both tables.
Rename the tables (optional): To make it easier to identify the tables later, you can rename them. To do this, go to the “Query Settings” pane on the right, click on “Name”, and type in a new name.
Merge the tables: Go to the “Home” tab, select “Combine”, and then “Merge”. A dialog box will appear. Here, you’ll need to select the two tables you want to merge (the shipment details table and the delivery dates table).
Select the join kind: In the same dialog box, you’ll need to choose a join kind. Since you want only the shipments present in both tables, you should select “Inner” (only matching rows).
Select the matching columns: Still in the dialog box, click on the column that contains the shipment IDs in both tables (these are your matching columns). Then click “OK”.
Expand the new table: After merging, your query will now include a new column that contains a table for each row. To expand this table and see all of its data, click on the double-arrow icon in this column’s header. A dialog box will appear allowing you to select which columns you want to include in your final table.
Load the new table into Excel: Once you’re done with all these steps, go to the “Home” tab, select “Close & Load”, and your new table will appear in Excel.
Remember that Power Query is case sensitive, so make sure your shipment IDs match exactly in both tables. If they don’t, you might need to clean your data before merging.
Form a table that displays shipments from the first table, even if they don’t have a matching ID in the second table.
Your Approach:
Sure, here’s a step-by-step approach to create a new table that includes all shipments from the first table, even if they don’t have a matching ID in the second table using Power Query:
Load the tables into Power Query: In Excel, go to the “Data” tab, select “Get Data”, then “From Other Sources”, and finally “From Table/Range”. Do this for both tables.
Rename the tables (optional): To make it easier to identify the tables later, you can rename them. To do this, go to the “Query Settings” pane on the right, click on “Name”, and type in a new name.
Merge the tables: Go to the “Home” tab, select “Combine”, and then “Merge”. A dialog box will appear. Here, you’ll need to select the two tables you want to merge (the shipment details table and the delivery dates table).
Select the join kind: In the same dialog box, you’ll need to choose a join kind. Since you want all shipments from the first table even if they don’t have a matching ID in the second table, you should select “Left Outer” (all from first, matching from second).
Select the matching columns: Still in the dialog box, click on the column that contains the shipment IDs in both tables (these are your matching columns). Then click “OK”.
Expand the new table: After merging, your query will now include a new column that contains a table for each row. To expand this table and see all of its data, click on the double-arrow icon in this column’s header. A dialog box will appear allowing you to select which columns you want to include in your final table.
Load the new table into Excel: Once you’re done with all these steps, go to the “Home” tab, select “Close & Load”, and your new table will appear in Excel.
Remember that Power Query is case sensitive, so make sure your shipment IDs match exactly in both tables. If they don’t, you might need to clean your data before merging.
Questions:
In the context of Power Query, what does an “Inner Merge” result in?
Answer:
i) A table with rows that have matching keys in both tables.
If you want to ensure that all data from both tables is retained in the merged table, which merge type should you use?
Answer: