🛠️ 5 More Useful Features in Power Query Editor with Explanation

Using this dataset, you can practice these advanced or frequently used features:

---

1. 🔍 Filter Rows Based on Conditions

Use Case: View only employees who worked in “New York”

---

🔧 Steps:

1. Open Power Query Editor.
2. Go to the column you want to filter — for example, Location.
3. Click the filter icon in the column header.
4. You’ll see a list of values (with checkboxes).
5. Either:
   • Uncheck all and select New York
   • Or choose Text Filters → Equals, then enter "New York" and click OK.

✅ Result: Only rows where Location is “New York” are kept.

Real-Life Benefit: Helpful when creating location-specific dashboards.

---

2. 🪞Time Column Cleaning

1. In Power Query Editor, select the CheckInTime column.
2. Go to Transform → Replace Values.
3. Replace . with :
   • Value to Find: .
   • Replace With: :
   This will convert 9.02.00 → 9:02:00, which is valid.
4. Change the Data Type to Time:
   • With the column selected, go to Transform → Data Type → Time
5. Repeat the same steps for the CheckOutTime column.

---

---

✅ Objective: Add a Custom Column HoursWorked

---

🔧 Steps to Add the HoursWorked Column

1. ✅ Open Power Query Editor
   • In Power BI, go to the Home tab → Transform Data.
2. ✅ Ensure Time Columns Are Proper
   • Make sure both CheckInTime and CheckOutTime are:
     • Converted from text to time
     • Format should be like: 09:15:00, not 9.15.00
3. ✅ Go to Add Column Tab
   • Click on Add Column → Custom Column
4. ✅ Enter Formula for Hours Worked In the dialog that opens:
   • New column name: HoursWorked
   • Formula: Duration.TotalHours(Time.From([CheckOutTime]) - Time.From([CheckInTime]))
5. ✅ Click OK
6. ✅ Change Data Type (Optional)
   • After the column is created, you may want to round or change the data type to Decimal Number.

---

📌 What This Does:

• Converts both times to Time values (if not already).
• Subtracts CheckInTime from CheckOutTime.
• Duration.TotalHours converts the resulting duration to total hours in decimal format.

Example:
CheckInTime = 09:00:00, CheckOutTime = 17:30:00 → HoursWorked = 8.5

---

3. 🧾 Group By

Use Case: Calculate total hours worked by each employee across dates.

Steps:

1. First, add a Custom Column for HoursWorked: Duration.TotalHours(Time.From([CheckOutTime]) - Time.From([CheckInTime]))
2. Go to Transform → Group By
   • Group by: EmployeeID, Name
   • Operation: Sum of HoursWorked

Result: Aggregated hours per employee.

---

4. 🧮 Conditional Column

Use Case: Tag employees as “Late” if CheckInTime > 09:30 AM

Steps:

• Go to Add Column → Conditional Column
• If CheckInTime > 09:30 → then “Late” else “On Time”

Result: A new column with values: Late or On Time.

---

5. 🔀 Merge Queries (Self Join to Compare Dates)

Use Case: Join this table to itself to compare if an employee worked on consecutive days.

Steps:

1. Duplicate the table in Power Query.
2. Merge original and duplicate on EmployeeID where date = date + 1.
3. Use merge kind: Inner Join
4. Expand to get info for the next day.

Result: Helps in analyzing streaks or consecutive working days.

---