Power Query – Summary and Exercises

Module 6: Summary and Final Exercises

Objective

Apply your Power Query skills in realistic, open-ended scenarios.

Files Used

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

Final Exercise 1: Combine and Analyze Sales Data

Goal: Merge January and February sales data and calculate revenue by region.

Steps:

1. Load both files into Power Query: Sales_January.xlsx and Sales_February.xlsx
2. Use Home tab → Append Queries
3. Remove any unnecessary columns
4. Ensure data types are correct
5. Group by Region and sum Total Sales
6. Sort results by total revenue

Final Exercise 2: Filter Active Employees

Goal: Filter active employees and prepare a clean export.

Steps:

1. Load Employees.csv
2. Remove unnecessary columns
3. Filter rows where Status = "Active"
4. Rename columns to be presentation-ready
5. Load the cleaned result into a worksheet

Additional Exercise: Full Transformation Chain

Goal: Simulate a real-life workflow combining multiple steps.

Steps:

1. Load both sales files
2. Append them
3. Add a new column to classify sales volume:
   • Low < 500
   • Medium 500–2000
   • High > 2000
4. Use conditional column or custom M
5. Group data by Product and Sales Level
6. Output to Excel as a PivotTable

Extension: Create a Mini-Report Using Power Pivot

Goal: Summarize total sales per region and volume category using Data Model.

Steps:

1. Load transformed sales data to the Data Model
2. Open Power Pivot → add a DAX measure for total revenue
3. Create a PivotTable using:
   • Rows: Region
   • Columns: Volume Category
   • Values: Total Revenue
4. Format the PivotTable for readability

---

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