Class Introduction
This was a Power Query training session focused on append and merge queries. The instructor explained the concept of appending queries by adding new data tables after existing ones, demonstrating how to append sales data from 2022 and 2023 using Excel workbooks. The session then covered different types of joins in merge queries, including inner joins, left joins, and left anti-joins using the Qatar Expo 2024 and 2025 datasets as examples. The instructor also explained when to use merge queries versus append queries, noting that merge is useful when there are no common columns between tables or when enriching data with additional information. The class concluded with a discussion on unpivoting data, which transforms combined attributes into separate columns to improve data modeling in Power BI, using a timeline dataset as an example.
Power Query Append and Merge
The instructor introduced a new topic on append and merge queries in Power Query, explaining the concept of appending data to existing tables. The instructor provided an example of bank transactions to illustrate how appending works, adding new data below existing data. The session ended with the instructor instructing the team to follow along with an exercise using the "Get Data" feature in Power Query, specifically connecting to an Excel workbook.
Power BI Data Transformation Techniques
The team discussed working with Power BI datasets, specifically focusing on transforming and appending sales data from 2022 and 2023. They demonstrated how to rename tables in the Power Query editor and explained the process of appending data when tables have the same columns, noting that additional columns will appear as null values in the resulting combined table.
Power BI Query Appending and Merging
The team discussed the process of appending and merging queries in Power BI. They explained the importance of matching column orders and data types when appending tables, and demonstrated how to properly append two sales datasets (Sales 2022 and Sales 2023) while ensuring correct data ordering. The instructor then introduced the concept of merging datasets by showing an example with product, tariffs, and regions data, explaining how to handle missing values in the tariff information.
Related Offerings
Power Query Data Table Merging
The team discussed merging data tables in Power Query, focusing on joining two tables containing event attendance data for Qatar Expo 2024 and 2025. They explained different types of joins including inner join, left join, and anti-joins, with inner join being used to find attendees who participated in both events. The team demonstrated how to merge the two tables using Power Query by selecting the common ID column and performing an inner join, resulting in a merged query showing six people who attended both events.
SQL Join Operations Discussion
The team discussed SQL join operations, specifically focusing on left joins to filter data between 2024 and 2025 records. They explained how columns and rows are merged during joins, resulting in combined columns from both tables, and demonstrated the use of expand functionality to view the merged data. The discussion included examples of inner joins and left anti-joins, with the team clarifying the purpose and applications of each type of join. The session concluded with plans to continue with more complex examples after a brief break.
Power BI Data Merge Demo
The team demonstrated a complex Power BI data merge example using tariff and product tables. They explained the concept of joining tables, specifically using a left outer join to add tariff details to the main product table. The team showed how to connect to Excel and CSV files, transform data by using the first row as headers, and set up the merge queries with the product table as the main table on the left side. The discussion highlighted that while product name was used as the common column for joining in this example, using IDs is the recommended approach for table joins in practice.
Power BI Data Transformation Techniques
The team discussed data transformation techniques in Power BI, focusing on merge queries and fuzzy matching for product data including tariffs and regions. They explained the concept of unpivoting, demonstrating how to transform combined attributes into separate columns to improve data modeling efficiency. The instructor recommended using "unpivot other columns" as the best practice approach for unpivoting, as it handles future data additions more flexibly than selecting specific columns. The session concluded with plans to cover SharePoint data import in the next class on Wednesday.
Ready to work with data from multiple sources more efficiently?
