View source for Power Query – Summary and Exercises

= Module 6: Summary and Final Exercises =

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

== Files Used ==
* [[Media:Sales_January.xlsx|Download Sales_January.xlsx]]
* [[Media:Sales_February.xlsx|Download Sales_February.xlsx]]
* [[Media:Employees.csv|Download Employees.csv]]

== Final Exercise 1: Combine and Analyze Sales Data ==
'''Goal:''' Merge January and February sales data and calculate revenue by region.

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

== Final Exercise 2: Filter Active Employees ==
'''Goal:''' Filter active employees and prepare a clean export.

'''Steps:'''
# Load '''Employees.csv'''
# Remove unnecessary columns
# Filter rows where '''Status''' = "Active"
# Rename columns to be presentation-ready
# Load the cleaned result into a worksheet

== Additional Exercise: Full Transformation Chain ==
'''Goal:''' Simulate a real-life workflow combining multiple steps.

'''Steps:'''
# Load both sales files
# Append them
# Add a new column to classify sales volume:
#* Low < 500
#* Medium 500–2000
#* High > 2000
# Use conditional column or custom M
# Group data by Product and Sales Level
# 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:'''
# Load transformed sales data to the Data Model
# Open Power Pivot → add a DAX measure for total revenue
# Create a PivotTable using:
#* Rows: Region
#* Columns: Volume Category
#* Values: Total Revenue
# Format the PivotTable for readability

----


Return to [[Power_Query_–_Basic|Main Page]]

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