Power Query for Analysts - Revision history

Gpruszczynski1: /* Task */

2025-09-17T18:43:58Z

Task

← Older revision		Revision as of 18:43, 17 September 2025
Line 748:		Line 748:

	=== Task ===		=== Task ===
	Below are 10 questions~~. Use only point-and-click operations inside Power Query (no manual M coding) to answer them~~.		Below are 10 questions.

	# List all employees together with their department name and location.		# List all employees together with their department name and location.
Line 759:		Line 759:
	# For each department, count how many different job titles are present.		# For each department, count how many different job titles are present.
	# Assign each employee a salary grade using the SALGRADE table.		# Assign each employee a salary grade using the SALGRADE table.
	# Find employees whose salary is above the average salary of their department.		# Find employees whose salary is above the average salary of their department.

	=== Deliverables ===		=== Deliverables ===

Gpruszczynski1: /* Data Sources */

2025-09-17T18:39:50Z

Data Sources

← Older revision		Revision as of 18:39, 17 September 2025
Line 708:		Line 708:
	In this exercise, we will use three small Excel files that simulate a classic HR database:		In this exercise, we will use three small Excel files that simulate a classic HR database:

	* 📊 emp.xlsx → Employees (ID, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)		'''Download files:'''
	* 📊 dept.xlsx → Departments (DEPTNO, DNAME, LOC)
	* 📊 salgrade.xlsx → Salary Grades (GRADE, LOSAL, HISAL)

	~~'''Download files:'''~~		* 📂 [[File:emp.xlsx]] → Employees (ID, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
	* [[:File:~~emp.xlsx\|Download~~ emp.xlsx]]		* 📂 [[File:dept.xlsx]] → Departments (DEPTNO, DNAME, LOC)
	* [[:File:~~dept.xlsx\|Download~~ dept.xlsx]]		* 📂 [[File:salgrade.xlsx]] → Salary Grades (GRADE, LOSAL, HISAL)
	* [[:File:~~salgrade.xlsx\|Download~~ salgrade.xlsx]]

	=== Instructions ===		=== Instructions ===

Gpruszczynski1: /* Module 11: Working with Relational Data in Power Query (EMP, DEPT, SALGRADE) */

2025-09-17T18:36:58Z

Module 11: Working with Relational Data in Power Query (EMP, DEPT, SALGRADE)

← Older revision		Revision as of 18:36, 17 September 2025
Line 695:		Line 695:


	== Module 11: Working with Relational Data in Power Query ~~(EMP, DEPT, SALGRADE)~~ ==		== Module 11: Working with Relational Data in Power Query ==

	=== Objective ===		=== Objective ===

Gpruszczynski1 at 18:36, 17 September 2025

2025-09-17T18:36:38Z

← Older revision		Revision as of 18:36, 17 September 2025
Line 693:		Line 693:
	* ✔ Create charts and comparison tables in Excel or Power BI		* ✔ Create charts and comparison tables in Excel or Power BI
	* ✔ Use ChatGPT if you encounter issues during import or analysis		* ✔ Use ChatGPT if you encounter issues during import or analysis


			== Module 11: Working with Relational Data in Power Query (EMP, DEPT, SALGRADE) ==

			=== Objective ===
			In this module, you will learn:

			* ✔ How to import multiple related tables into Power Query
			* ✔ How to use Merge Queries to combine employee, department, and salary grade data
			* ✔ How to perform basic aggregations and grouping using the Group By feature
			* ✔ How to answer typical business questions using point-and-click operations in Power Query

			=== Data Sources ===
			In this exercise, we will use three small Excel files that simulate a classic HR database:

			* 📊 emp.xlsx → Employees (ID, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
			* 📊 dept.xlsx → Departments (DEPTNO, DNAME, LOC)
			* 📊 salgrade.xlsx → Salary Grades (GRADE, LOSAL, HISAL)

			'''Download files:'''
			* [[:File:emp.xlsx\|Download emp.xlsx]]
			* [[:File:dept.xlsx\|Download dept.xlsx]]
			* [[:File:salgrade.xlsx\|Download salgrade.xlsx]]

			=== Instructions ===

			🔹 '''Step 1: Import all three files'''

			# Open Excel → Data → Get Data → From File → From Workbook
			# Select emp.xlsx, then repeat for dept.xlsx and salgrade.xlsx
			# You will now see three queries in the Power Query editor: `EMP`, `DEPT`, and `SALGRADE`

			🔹 '''Step 2: Explore the EMP table'''

			# Check column data types (e.g. SAL should be number, HIREDATE should be date)
			# Remove unnecessary columns if needed
			# Rename columns to user-friendly names (e.g. Employee Name, Department No, Salary)

			🔹 '''Step 3: Combine EMP and DEPT'''

			# Use Home → Merge Queries
			# Join EMP.DEPTNO with DEPT.DEPTNO
			# Expand columns from DEPT (Department Name, Location)

			🔹 '''Step 4: Join EMP with SALGRADE'''

			# Use Merge Queries again
			# Match EMP.SAL with SALGRADE ranges: SAL between LOSAL and HISAL
			# (Hint: do a cross join with helper column =1 in both tables, then filter SAL between LOSAL and HISAL)
			# Expand the GRADE column

			🔹 '''Step 5: Analyze and group data'''

			# Use Group By to count employees per department
			# Add aggregations such as Min Salary, Max Salary, Average Salary
			# Create new columns with custom calculations if needed (e.g. Salary Difference = Max − Min)

			=== Task ===
			Below are 10 questions. Use only point-and-click operations inside Power Query (no manual M coding) to answer them.

			# List all employees together with their department name and location.
			# Find all employees who have a commission (COMM) value greater than 0.
			# Show the highest salary in each department.
			# Calculate the average salary of managers vs. salesmen (group by JOB).
			# Find the department(s) that have no employees assigned.
			# Display employees hired in 1981, sorted by hire date.
			# Show the top 3 salaries in the Sales department.
			# For each department, count how many different job titles are present.
			# Assign each employee a salary grade using the SALGRADE table.
			# Find employees whose salary is above the average salary of their department.

			=== Deliverables ===
			* ✔ Import and combine EMP, DEPT, SALGRADE into Power Query
			* ✔ Use Merge Queries to enrich employees with department and grade information
			* ✔ Apply Group By and filters to answer the 10 tasks above
			* ✔ Load the transformed data into Excel or Power BI for reporting
			* ✔ Practice solving relational queries with Power Query UI only (no coding)

Gpruszczynski1: /* Instructions */

2025-06-24T12:47:32Z

Instructions

← Older revision		Revision as of 12:47, 24 June 2025
Line 552:		Line 552:

	<pre>		<pre>
	if [OnTimeDeliveryRate] ~~> 95~~ then "Excellent"		if [OnTimeDeliveryRate] < 80 then "Excellent"
	else if [OnTimeDeliveryRate] >= 80 then "Good"		else if [OnTimeDeliveryRate] <= 90 then "Good"
	else "Poor"		else "Poor"
	</pre>		</pre>

Gpruszczynski1: /* Data Sources */

2025-06-22T19:05:16Z

Data Sources

← Older revision		Revision as of 19:05, 22 June 2025
Line 652:		Line 652:
	* [https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_area List of countries by area]		* [https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_area List of countries by area]
	* [https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population List of countries by population]		* [https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population List of countries by population]
	* [https://pl.wikipedia.org/wiki/~~Lista_pa%C5%84stw_%C5%9Bwiata_wed%C5%82ug_PKB_nominalnego~~ List of countries by nominal GDP]		* [https://en.wikipedia.org/wiki/List_of_countries_by_GDP_(nominal) List of countries by nominal GDP]

	=== Instructions ===		=== Instructions ===

Gpruszczynski1: /* Data Sources */

2025-06-22T19:04:40Z

Data Sources

← Older revision		Revision as of 19:04, 22 June 2025
Line 651:		Line 651:
	'''Sources:'''		'''Sources:'''
	* [https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_area List of countries by area]		* [https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_area List of countries by area]
	* [https://pl.wikipedia.org/wiki/~~Lista_pa%C5%84stw_%C5%9Bwiata_wed%C5%82ug_liczby_ludno%C5%9Bci~~ List of countries by population]		* [https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population List of countries by population]
	* [https://pl.wikipedia.org/wiki/Lista_pa%C5%84stw_%C5%9Bwiata_wed%C5%82ug_PKB_nominalnego List of countries by nominal GDP]		* [https://pl.wikipedia.org/wiki/Lista_pa%C5%84stw_%C5%9Bwiata_wed%C5%82ug_PKB_nominalnego List of countries by nominal GDP]

Gpruszczynski1: /* Module 10: Importing and Analyzing Web Data in Power Query */

2025-06-22T19:04:09Z

Module 10: Importing and Analyzing Web Data in Power Query

← Older revision		Revision as of 19:04, 22 June 2025
Line 650:		Line 650:

	'''Sources:'''		'''Sources:'''
	* [https://pl.wikipedia.org/wiki/~~Lista_pa%C5%84stw_%C5%9Bwiata_wed%C5%82ug_powierzchni~~ List of countries by area]		* [https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_area List of countries by area]
	* [https://pl.wikipedia.org/wiki/Lista_pa%C5%84stw_%C5%9Bwiata_wed%C5%82ug_liczby_ludno%C5%9Bci List of countries by population]		* [https://pl.wikipedia.org/wiki/Lista_pa%C5%84stw_%C5%9Bwiata_wed%C5%82ug_liczby_ludno%C5%9Bci List of countries by population]
	* [https://pl.wikipedia.org/wiki/Lista_pa%C5%84stw_%C5%9Bwiata_wed%C5%82ug_PKB_nominalnego List of countries by nominal GDP]		* [https://pl.wikipedia.org/wiki/Lista_pa%C5%84stw_%C5%9Bwiata_wed%C5%82ug_PKB_nominalnego List of countries by nominal GDP]

Gpruszczynski1: /* Instructions */

2025-06-22T18:56:05Z

Instructions

← Older revision		Revision as of 18:56, 22 June 2025
Line 191:		Line 191:
	# Click Transform → Unpivot Columns.		# Click Transform → Unpivot Columns.
	# Rename the resulting columns:		# Rename the resulting columns:
	`"Attribute"` → `Month`		## Attribute → Month
	`"Value"` → `Sales Amount`		## Value → Sales Amount

	<br>		<br>

Gpruszczynski1: /* Instructions */

2025-06-22T18:53:08Z

Instructions

← Older revision		Revision as of 18:53, 22 June 2025
Line 141:		Line 141:
	Write an M function that takes `Cost` as input and returns a category:		Write an M function that takes `Cost` as input and returns a category:
	Low if Cost < 500 Medium if Cost between 500 and 1500 High if Cost > 1500		Low if Cost < 500 Medium if Cost between 500 and 1500 High if Cost > 1500
	~~Tip: If you're unsure how to construct the function, ask ChatGPT:~~
	~~"How to write an M function that categorizes prices into Low, Medium, and High?"~~

	<br>		<br>