Power Query Editor 3 different features of it on Employee Data

✅ Sample Dataset: Employee Work Hours (Synthetic)

EmployeeID	Name	Department	Date	CheckInTime	CheckOutTime	Location
E001	Alice	HR	2024-06-01	09:15	17:45	New York
E002	Bob	Finance	2024-06-01	09:30	18:00	Chicago
E003	Charlie	IT	2024-06-01	10:00	19:00	San Francisco
E004	Diana	HR	2024-06-01	09:00	17:00	New York
E005	Edward	IT	2024-06-01	11:00	20:00	Chicago

Here’s a mini tutorial on Power Query Editor usage in Power BI, covering 3 different features (other than basic formatting), using a sample dataset.

---

---

🔧 3 Useful Power Query Editor Features (Beyond Basic Formatting)

---

1. 🧮 Create a Custom Column (Duration Calculation)

Objective: Calculate total hours worked by each employee.

Steps:

• Go to Add Column → Custom Column
• Use formula: Duration.TotalHours(Time.From([CheckOutTime]) - Time.From([CheckInTime]))
• Name the column as HoursWorked

Result: A new column with the number of hours each employee worked that day.

---

2. 🧹 Remove Duplicates

Objective: Clean data by removing duplicate entries based on EmployeeID and Date.

Steps:

• Select both columns: EmployeeID and Date
• Go to Home → Remove Rows → Remove Duplicates

When to Use: If your dataset has duplicate clock-in/out records for the same employee on the same day.

---

3. 🔄 Pivot Column

Objective: Transform data to show each employee’s total hours by location.

Steps:

• First, ensure you’ve added the HoursWorked column.
• Go to Transform → Pivot Column
• Choose Location as the column to pivot.
• Use HoursWorked as the values column and select “Sum” as the aggregation.

Result: You’ll get one row per employee with columns like New York, Chicago, San Francisco, showing the sum of hours worked at each location.

---

📘 Summary Table of Features

Feature	Purpose	Where to Find
Custom Column	Add calculated logic	Add Column → Custom Column
Remove Duplicates	Data cleaning	Home → Remove Rows → Duplicates
Pivot Column	Reshape tabular data	Transform → Pivot Column

---