Power Query – Summary and Exercises: Difference between revisions

From Training Material
Jump to navigation Jump to search
← Older edit
No edit summary
No edit summary
 
Line 2: Line 2:


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


== Files Used ==
== Files Used ==
Line 10: Line 9:
* [[Media:Employees.csv|Download Employees.csv]]
* [[Media:Employees.csv|Download Employees.csv]]


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


'''Goal:''' Combine two months of sales data, clean it, and calculate total revenue per 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:'''
'''Steps:'''
# Load both '''Sales_January.xlsx''' and '''Sales_February.xlsx''' into Power Query
# Load '''Employees.csv'''
# Combine them using '''Append Queries'''
# Remove unnecessary columns
# Remove unnecessary columns
# Ensure data types are correct (e.g. Date, Text, Whole Number)
# Filter rows where '''Status''' = "Active"
# Group data by '''Region''' and calculate total sales
# Rename columns to be presentation-ready
# Sort results by descending revenue
# Load the cleaned result into a worksheet


== Final Exercise – Employee Filtering and Export ==
== Additional Exercise: Full Transformation Chain ==
 
'''Goal:''' Simulate a real-life workflow combining multiple steps.
'''Goal:''' Filter active employees from the HR system and export the data.


'''Steps:'''
'''Steps:'''
# Load '''Employees.csv''' into Power Query
# Load both sales files
# Remove unnecessary columns
# Append them
# Filter rows where '''Status''' equals '''Active'''
# Add a new column to classify sales volume:
# Rename columns as needed
#* Low < 500
# Load the cleaned data into a new worksheet
#* Medium 500–2000
#* High > 2000
# Use conditional column or custom M
# Group data by Product and Sales Level
# Output to Excel as a PivotTable


== Optional Bonus Task ==
== Extension: Create a Mini-Report Using Power Pivot ==
Try to convert the combined sales data to a PivotTable using Power Pivot:
'''Goal:''' Summarize total sales per region and volume category using Data Model.


* Load the combined query to the Data Model
'''Steps:'''
* Add a calculated measure (e.g. Total Revenue)
# Load transformed sales data to the Data Model
* Use PivotTable to analyze revenue by Region and Product
# 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


----
----

Latest revision as of 09:52, 19 June 2025

Module 6: Summary and Final Exercises

Objective

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

Files Used

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

Retrieved from "https://training-course-material.com/index.php?title=Power_Query_–_Summary_and_Exercises&oldid=88894"