Power Query – Summary and Exercises - Revision history

Gpruszczynski1 at 09:52, 19 June 2025

2025-06-19T09:52:09Z

← Older revision		Revision as of 09:52, 19 June 2025
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

	----		----

Gpruszczynski1 at 09:18, 19 June 2025

2025-06-19T09:18:18Z

← Older revision		Revision as of 09:18, 19 June 2025
Line 1:		Line 1:
			= 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 ==
			* [[Media:Sales_January.xlsx\|Download Sales_January.xlsx]]
			* [[Media:Sales_February.xlsx\|Download Sales_February.xlsx]]
			* [[Media:Employees.csv\|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:'''
			# Load both '''Sales_January.xlsx''' and '''Sales_February.xlsx''' into Power Query
			# Combine them using '''Append Queries'''
			# Remove unnecessary columns
			# Ensure data types are correct (e.g. Date, Text, Whole Number)
			# Group data by '''Region''' and calculate total sales
			# Sort results by descending revenue

			== Final Exercise – Employee Filtering and Export ==

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

			'''Steps:'''
			# Load '''Employees.csv''' into Power Query
			# Remove unnecessary columns
			# Filter rows where '''Status''' equals '''Active'''
			# Rename columns as needed
			# 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 [[Power_Query_–_Basic\|Main Page]]		Return to [[Power_Query_–_Basic\|Main Page]]

Gpruszczynski1 at 17:52, 18 June 2025

2025-06-18T17:52:03Z

← Older revision		Revision as of 17:52, 18 June 2025
Line 1:		Line 1:
	Return to [[Power_Query_–_Basic\|Main Page]]		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]]

Gpruszczynski1: Created page with "Return to Main Page"

2025-06-18T17:50:23Z

Created page with "Return to Main Page"

New page

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