Power Query – Summary and Exercises

Module 6: Summary and Final Exercises

Objective

In this final module, you'll apply the knowledge from previous modules in a guided challenge. The goal is to consolidate your skills using real-world data transformation scenarios.

Files Used

• Download Sales_January.xlsx
• Download Sales_February.xlsx
• Download Employees.csv

Final Exercise – Sales Data Cleanup and Analysis

Goal: Combine two months of sales data, clean it, and calculate total revenue per region.

Steps:

1. Load both Sales_January.xlsx and Sales_February.xlsx into Power Query
2. Combine them using Append Queries
3. Remove unnecessary columns
4. Ensure data types are correct (e.g. Date, Text, Whole Number)
5. Group data by Region and calculate total sales
6. Sort results by descending revenue

Final Exercise – Employee Filtering and Export

Goal: Filter active employees from the HR system and export the data.

Steps:

1. Load Employees.csv into Power Query
2. Remove unnecessary columns
3. Filter rows where Status equals Active
4. Rename columns as needed
5. Load the cleaned data into a new worksheet

Optional Bonus Task

Try to convert the combined sales data to a PivotTable using Power Pivot:

• Load the combined query to the Data Model
• Add a calculated measure (e.g. Total Revenue)
• Use PivotTable to analyze revenue by Region and Product

---

Return to Main Page

• Module 1: Introduction to Power Query
• Module 2: Importing and Managing Data
• Module 3: Data Transformation Techniques
• Module 4: Automation and Parameters
• Module 5: Power Pivot Overview
• Module 6: Summary and Final Exercises