Class Introduction
The class focused on a comprehensive recap and hands-on practice of Power Query in Power BI, covering data cleaning, transformation, merging, and appending. The instructor guided participants through exercises using sample datasets, demonstrating tasks such as renaming tables, calculating new columns, grouping data, filtering null values, and using column from examples for pattern matching. The session also explained different types of joins for merging queries, including inner, left outer, and left anti-joins, with practical examples using event datasets. Additional topics included transposing and unpivoting tables to restructure data for better modeling. The instructor emphasized the importance of data cleaning before moving on to data modeling, which was announced as the next topic. The class concluded with a reminder about homework and the next session.
Class Revision Session
The team began a class with a plan to conduct a short revision of previous topics due to a long gap since the last class. They intended to recap the covered material before moving on to new content, but no students responded when asked about remembered topics. The instructor decided to provide a recap themselves and list the topics covered.
Power Query Data Transformation Exercise
The team conducted a hands-on exercise in Power Query for data cleaning and transformation. They practiced loading data from Excel workbooks and PDF invoices, performing basic transformations like renaming tables, calculating duration differences between dates, and removing null values. The session included advanced techniques such as grouping data by resource names and working with combined data from multiple files, with specific guidance on handling data types and pattern matching for numeric values embedded in text columns.
Related Offerings
Data Transformation and Cleaning Techniques
The team demonstrated data transformation techniques in a spreadsheet application, focusing on cleaning and restructuring invoice data. They showed how to remove unnecessary columns, extract and clean invoice numbers from source names, and perform grouping operations to calculate total amounts by description. The session concluded with instructions on how to append data from an Excel workbook containing sales data from 2022.
Power BI Sales Data Appending
The team demonstrated how to append two sales data tables (2022 and 2023) into a single continuous table using Power BI. They explained that appending queries work only when the source tables have identical column structures and headings, which is common in corporate environments where data is generated from consistent systems like Google Forms or CRMs. The team also briefly mentioned that merge queries can be used when dealing with different tables without matching columns, and they shared a helpful blog link from Team Academy for further reference on query merging techniques.
Power BI Query Joins Overview
The team discussed different types of joins used when merging queries and tables in Power BI and Power Query, including left outer join, right outer join, full outer join, inner join, left anti-join, and right anti-join. They explained the purpose and mechanics of each join type, emphasizing the use of common columns, like ID, to link tables. The instructor planned to practice each join type separately and work through an example before taking a short break.
Database Joins and Data Modeling
The team reviewed different types of database joins including inner joins, left anti-joins, and left outer joins using Power BI and Power Query. They practiced merging two Expo datasets to find common attendees between 2024 and 2025, and demonstrated how to merge a products table with a tariffs table using fuzzy matching for product names. The session concluded with a discussion on data transposing and unpivoting, setting up for future data modeling lessons starting September 1st.
Ready to master data preparation before building powerful Power BI reports?
