Class Introduction
This was a Power BI training session focused on data cleaning and transformation techniques. The instructor guided participants through various data manipulation exercises using Power Query, including grouping data by employee names to calculate total working hours and repairs attended, cleaning PDF invoice data by removing empty columns and converting data types, and performing advanced transformations like splitting columns and creating custom formulas. The session covered practical examples such as extracting invoice numbers from source names, calculating task completion duration by subtracting end dates from start dates, and splitting employee names into first and last names. Participants practiced using different Power Query features including group by operations, custom columns, merge columns, and data type conversions, with the instructor providing step-by-step guidance and troubleshooting assistance throughout the hands-on exercises.
Power BI Data Transformation Workshop
The team discussed how to work with data cleaning and transformation in Power BI, building on previous lessons about data retrieval from different file types. They walked through the process of accessing and combining data from the Power Query Practice folder, specifically focusing on weekly reports containing employee performance data for Mike, Jason, and Bernard. The instructor explained that the combined data shows weekly metrics including total working hours, repairs attended, and status for each employee across multiple weeks.
Excel Data Grouping Techniques
The team discussed how to group and summarize data by employee working hours and repairs attended using Excel's Group By feature. They demonstrated both basic and advanced grouping methods, showing how to create summaries by resource name and add calculations for total working hours and repairs. The session included troubleshooting assistance for participants and explained the difference between basic grouping and advanced grouping with additional layers like company names.
Power BI Data Reporting Techniques
The team discussed creating multiple reports from the same data source in Power BI. They explained how to create different presentations of the same data by importing the weekly report multiple times and applying different groupings to generate separate queries with the same source data. The discussion then moved to working with PDF invoices, where they identified and removed the taxes column due to its empty and null values.
Related Offerings
Power BI Sales Data Processing
The team discussed how to group and sum sales data by course description in Power BI. They identified that the amount column contained both numeric and text data (including currency symbols like "QR"), which prevented proper mathematical operations. The team learned they needed to extract just the numeric portion of the amount using the "Columns from Example" feature in the Add Column tab, then convert the resulting column to a decimal data type before performing the group by operation on the description column.
Power BI Sales Data Integration
The team worked on combining and filtering sales data in Power BI, including handling PDF invoices and refreshing data to show updates. They discussed the importance of proper data validation and access controls, noting that data analysts handle these aspects while dashboard creators focus on visualization. The team encountered some technical issues with file locations and data types, particularly with converting the amount column to decimal numbers and ensuring the correct file path was being used.
File Management and Data Organization
The team discussed file management procedures, emphasizing the importance of using exact source files from the downloads folder and proper copying methods. They worked on modifying a table by extracting invoice numbers from the source name column and removing PDF extensions. The team also explored options for removing duplicate rows and discussed the possibility of aggregating data by invoice number for better organization.
Power Query Timeline Dataset Processing
The team discussed using Power Query to work with a new timeline dataset containing task information, including start and end dates, costs, and employee contributions. They demonstrated how to calculate the number of days taken to complete tasks by subtracting start dates from end dates using the Power Query Editor. The team also showed how to rename queries and tables for better organization, and they began navigating to a new CSV file for further data processing.
Power Query Column Transformations
The team discussed Power Query transformations, focusing on splitting columns and merging data. They demonstrated how to split the employee name column into first name and last name using the split column feature, and then showed two methods to create a full name column - using the Merge Columns function in the Transform tab and creating a custom column with a formula. The session also covered adding a new column for total sales calculation using a custom column formula combining unit price, quantity, and discount. The instructor announced that more advanced transformation topics like merging, appending, unpivoting, and transposing would be covered in the next class.
Ready to clean, transform, and prepare real-world data for powerful Power BI reporting?
👉 Explore our Power BI Data Analytics Training Program and master Power Query, data cleaning, custom columns, grouping, merging, and advanced data transformation techniques through hands-on exercises.
