Class Introduction
The class focused on data transformation techniques in Power Query, specifically covering append and merge queries. The instructor explained the concept of appending, demonstrating how to combine sales data from 2022 and 2023 into a single table, emphasizing that the columns must match. The session then delved into merging queries, discussing various join types such as inner, left, right, full, left anti, and right anti, using examples from event and product datasets to illustrate their use cases. The instructor also explained the importance of data enrichment and filtering through merging. The class concluded with an introduction to transposing and unpivoting data, explaining why unpivoting is crucial for optimizing data for Power BI's data modeling.
Power Query Appending and Merging
The team discussed Power Query operations, focusing on appending and merging queries. They demonstrated how to append data from Excel workbooks containing sales data for 2022 and 2023, emphasizing the requirement for matching column structures. The instructor explained the concept of appending by showing how to load and rename data tables, and highlighted the importance of having identical columns when using append queries.
Append and Merge Query Processes
The team explained the process of Append Queries, demonstrating how to combine two tables (2022 and 2023 data) into a single appended table while maintaining the original tables. They then introduced Merge Queries, explaining that it's used to combine data from different tables, particularly when recombining information from separate tables like customers, addresses, orders, and reviews into a comprehensive dataset. The discussion highlighted that different types of joins can be applied during merging to create combined tables with integrated data from multiple sources.
Related Offerings
Data Merge Operations in Power BI
The team discussed when to use merge operations in data processing, identifying three key scenarios: creating a big picture view, data enrichment by adding extra information to reference tables, and filtering data to focus on active customers or specific information. They explained different types of joins including inner join, left join, and full join, and provided a hands-on demonstration using Power BI and Power Query to work with the Merge Datasets folder containing event data from 2024 and 2025 Qatar Expo.
SQL Joins Types and Applications
The team discussed different types of SQL joins, including inner join, left join, right join, full join, and anti-joins. They explained how each join type works using examples with tables containing data from 2024 and 2025 events, demonstrating how to find matching, left-only, and right-only data rows. The discussion focused on practical applications of these joins, with the team noting that right joins are rarely used as left joins can achieve the same result by flipping the table order.
Table Merge and Data Join
The team discussed merging two tables: a product table containing product details and a tariff table providing information about product tariffs and regions. They explained the concept of using a master table (product table) and a reference table (tariff table) for data enrichment, and decided to perform a left outer join to include all product information along with matching tariff data. The team demonstrated how to use the "Use First Row as Header" option in the Transform tab to properly structure the data and renamed the sheet to "Tariffs."
Power BI Data Transformation Techniques
The team discussed data merging and transformation techniques in Power BI, focusing on fuzzy matching, column selection, and table expansion. They explained the importance of using IDs for merging and demonstrated how to use fuzzy matching when exact matches fail. The session covered transposing and unpivoting data, with detailed explanations of why unpivoting is necessary for data modeling in Power BI. The instructor emphasized the importance of unpivoting "other columns" rather than selected columns to accommodate future data changes and new additions. The class concluded with plans to move on to data modeling in the next session.
Ready to work with multiple datasets and build cleaner Power BI models?
