hr-people-analytics

## 📂 Datasets Used

| File | Rows | Description |

|------|------|-------------|

| HR-Analytics-Employee-Master.xlsx | 200 | Core employee data |

| HR-Analytics-Compensation-History.xlsx | 436 | Salary change history |

| HR-Analytics-Performance-Reviews.xlsx | 498 | Employee performance reviews |

| HR-Analytics-Leave-Attendance.xlsx | 1,000 | Leave and attendance records |

| HR-Analytics-Recruitment-Funnel.xlsx | 2,745 | Recruitment pipeline data |

**Total: 4,879 rows of HR data**

---

## 🔧 Tools & Skills

- **Power Query** — Data cleaning, custom columns, null handling

- **Power BI** — HR data modeling, DAX measures, interactive dashboards

- **DAX** — CALCULATE, COUNTROWS, DIVIDE, AVERAGE, FILTER

- **HR Concepts** — Turnover Rate, Hired Rate, Absenteeism, Tenure Analysis

---

## 🧹 Data Cleaning (Power Query)

- Converted date columns (HireDate, TerminationDate, ChangeDate, ReviewDate, StartDate)

- Applied Text.Trim to all text columns

- Replaced null values:

- `Gender` → "Not Specified"

- `#N/A` values → "Unknown"

- Numeric nulls → 0

- Date nulls (TerminationDate, ManagerID) → kept as null (meaningful data)

- Removed errors with Remove Rows → Remove Errors

- Added calculated columns:

**Employee Master:**

- `Years of Service` = (Today - HireDate) / 365

- `Tenure Band` = < 1 Year / 1-3 / 3-5 / 5-10 / 10+ Years

- `Status Label` = Active / Terminated / On Leave

- `Salary Band` = Entry / Mid / Senior / Executive

**Compensation History:**

- `Salary Change %` = (NewSalary - OldSalary) / OldSalary × 100

- `Raise Band` = No Raise / Small / Medium / Large

**Performance Reviews:**

- `Performance Label` = Excellent / Good / Average / Needs Improvement

- `Avg Score` = (Communication + Teamwork + ProblemSolving) / 3

**Recruitment Funnel:**

- `Days to Hire` = DateatStage - ApplicationDate

- `Stage Label` = Applied / Interview / Offer / Hired / Rejected

---

## 🗂️ Data Model

- Built a **Star Schema** with Date Table at the center

- Date Table covers 2018–2026

- Relationships via Date:

- Date → Employee Master (HireDate)

- Date → Compensation History (ChangeDate)

- Date → Performance Reviews (ReviewDate)

- Date → Leave Attendance (StartDate)

- Date → Recruitment Funnel (ApplicationDate)

- Relationships via EmployeeID:

- Employee Master → Compensation History

- Employee Master → Performance Reviews

- Employee Master → Leave Attendance