Class Notes - Introduction
This was a Power Query training session where Subhansi taught the team data cleaning and transformation techniques. The class covered importing data from Excel files and folders, combining multiple files, and performing basic data cleaning operations. Subhansi demonstrated how to group data by employee names to calculate total working hours and repairs, explained the difference between empty cells and null values, and showed techniques for removing unwanted characters from columns using column from example and replace value functions. The team practiced working with date columns to calculate the number of days between start and end dates, learned how to split text columns into first and last names using delimiters, and created custom columns through M-language formulas. Subhansi also covered data type conversions and rounding operations, while emphasizing the importance of proper column naming for better data understanding and collaboration. The session concluded with a brief introduction to upcoming topics including append queries and merge queries for future classes.
Power Query Data Cleaning Techniques
Subhansi led a session on Power Query, focusing on data cleaning techniques. The team discussed combining and transforming data from Excel files, specifically working with a weekly report dataset. Subhansi explained the concept of data grouping, using the example of combining working hours for duplicate employee names like Mike, Jason, and Bernard to create totals for each individual. The session covered how to use the Group By feature in Power Query to achieve this data consolidation.
Excel Data Cleaning Techniques
Subhansi demonstrated data cleaning and grouping techniques in Excel, focusing on proper column naming and filtering out null values. He showed how to perform basic and advanced grouping operations, including adding total working hours and number of repairs per employee. Subhansi also introduced data type concepts and rounding functions in the transform tab, allowing participants to practice rounding up and down values in their datasets.
Power BI Decimal Number Setup
The team discussed how to set up two-digit decimal numbers in Power BI, with the team member explaining that users can specify the desired decimal places in the rounding settings. They walked through the process of importing and transforming a PDF invoice table, including how to navigate to the correct folder and combine tables. The discussion concluded with instructions on how to properly remove columns, emphasizing the use of the "Remove Columns" option rather than the keyboard delete button to avoid errors.
Power Query Data Cleaning Exercises
The team worked on data cleaning and grouping exercises in Power Query, focusing on removing null values and handling alphanumeric amounts containing "QR" suffixes. Gagan and the team learned the difference between null values and empty cells, and practiced using column replacement and pattern matching techniques to extract and format the amount data properly. They successfully completed a group-by operation on the description column with sum calculations, and the session ended with instructions on how to extract only the invoice number from the source file name.
Related Offerings
Power Query Data Cleaning Workshop
The team discussed working with Power Query, focusing on cleaning and restructuring data. They demonstrated how to select specific columns, remove duplicates, and handle PDF-related issues in the data. The instructor guided participants through renaming columns and reordering them, with Gagan seeking assistance to resolve specific technical issues. The session concluded with plans to work on date values in a new exercise using the Timeline Dataset from an Excel Workbook.
Task Tracking System Calculations
The team discussed how to calculate the number of days between start and end dates in a task tracking system. They demonstrated the process of selecting multiple columns using the control key, adding a subtract days calculation, and renaming the resulting column. The discussion then shifted to explaining CSV files and their structure, followed by practicing with the Northwind Traders dataset by opening and examining the employees.csv file. The team concluded by discussing the importance of understanding why null values exist before removing them, using the "reports to" field as an example to demonstrate that null values can sometimes be valid data points.
Employee Name Column Splitting Demonstration
Subhansi demonstrated how to split an employee name column into first name and last name columns using a delimiter, specifically a space character. He explained the different options for splitting columns by number of characters or positions, and showed how to properly select and merge the resulting columns to create a full name column. The team followed along with the demonstration and confirmed they understood the process.
Power Query Custom Columns Training
The team learned how to create custom columns in Power Query using M language syntax. They practiced creating a full name column by concatenating first name and last name with a space using the ampersand operator. The group then calculated total sales for an order details dataset by multiplying unit price, quantity, and applying the discount. The instructor demonstrated how to modify existing formulas through the custom step settings. The session concluded with plans to cover append and merge queries in future lessons.
Ready to master data preparation techniques in Power BI?
