Entering Data in Excel – Settings
For cleaner data, we need to define what are acceptable values in each of the fields. We do this in the Settings sheet.
DO NOT CHANGE COLUMN HEADERS OR FIELD NAMES IN ROW 6. YOU CAN CHANGE IN ROW 5 AND THEN MODIFY NAMES IN POWER BI. EMAIL [email protected] FOR ASSISTANCE.
CHANGING FIELD NAMES WILL BREAK THE POWER BI REPORT
Enter the data for Gender, Hire Source and Ethnicity fields.
Enter list of Office Locations and the City/State/Country information.
Enter list of Departments and Job classification fields (Job Type, Job Category and Job Level).
Enter list of Job Titles
Enter list of Onboarding activities in your company.
Enter list of Exit types and Exit reasons you want to capture from employees who are leaving the company.
Enter list of reasons why an employee’s profile will change. For example, when an employee gets promoted, gets a salary increase, or changes teams.
Enter which days of the week are weekends and which days are company holidays. For weekends, just enter 1 next to the day. 0 represents a working day and not a weekend.
Entering Data in Excel – Salary Grades
Enter the list of Salary Grades in your company. Grade name, Minimum , Midpoint and Maximum Salaries are required.
By Default, you can enter up to 50 Salary Grades.
Entering Data in Excel – List of Employees
Enter list of all employees in your company.
If you need historical reporting, please make sure that employees from the past are also entered.
- Each employee should have a unique Employee Number.
- Each Employee should have a start date (date when they joined the company) and Date of birth (used for reporting on age)
- Gender and Ethnicity are used for Diversity reporting
- Office Location and Department fields are used in reporting within specific departments and locations.
- Jobs can be classified in 3 levels – Job Type, Job Category, Job level. In addition Job Title defines the employee’s specific job title.
- Manager name will be one of the employees in the company.
- Salary and Grade at the time of starting are entered.
Please do not enter more than one record for a single employee.
By Default, you can enter up to 600 employees.
Entering Data in Excel – Employee Updates
When an employee profile changes, please enter the new information in the EMPLOYEE UPDATES sheet.
Choose Employee, enter the effective date of profile change, and reason.
Choose a reason why the employee’s profile changed. For example, when an employee gets promoted, gets a salary increase, or changes teams.
Please do not enter more than one record on the same date for a single employee.
By Default, you can enter up to 3000 employee update records.
Entering Data in Excel – Employee Exits
When an employee leaves the company, please enter in the EXITS sheet.
Please do not enter more than one record on the same date for a single employee.
By Default, you can enter up to 300 employee exits.
Entering Data in Excel – Onboarding
To track the employees’ completion of Onboarding steps, please enter the information in the Onboarding sheet.
Employees will be automatically populated and onboarding steps will be displayed automatically.
You have to mark “Yes” if the employee completed a specific step. Then, Enter the Onboarding Status (Completed or In Progress or Not Started) and enter the completed date if completed the whole onboarding process.
Ongoing Data Entry
Now that we have covered all the input points in the Excel file, let’s take a look at when you should update the file with new data.
- When a new employee joins, please add a row to the EMPLOYEES table.
- When a change happens in employee profile (except exit or termination), enter a record in EMPLOYEE UPDATES table
- When an employee leaves, enter a new record in EXITS table
- When an employee completes onboarding step, enter in the ONBOARDING table.
For next step on how to set parameters in Power BI, please visit this article
Setting up Parameters and connection in Power BI