Créer un dataset RH dans Excel

Générez et auditez 2 000 données RH synthétiques avec Excel et Power Query.

Introduction

30 Second Summary

Une analyse RH peut sembler convaincante même lorsque ses données contiennent des doublons ou des dates impossibles. Sans règles claires, chaque graphique repose sur une base difficile à défendre.

Dans ce projet, vous allez créer dans Microsoft Excel for Microsoft 365 un jeu de données synthétiques de 2 000 employés avec Power Query. Vous appliquerez des règles métier cohérentes avant de contrôler la qualité du résultat.

What You'll Build

À l’actualisation, vous verrez une table de 2 000 employés prête à être filtrée pour analyser l’attrition dans une entreprise française multisite.

À la fin de ce projet, vous aurez :

  • Un dataset RH reproductible avec 2 000 employés identifiés de EMP-0001 à EMP-2000 sur 16 colonnes prêtes pour l’analyse.
  • Des profils employés cohérents où l’âge encadre l’ancienneté. Le département détermine aussi les postes disponibles et les bases salariales.
  • Un classeur auditable avec un dictionnaire des variables et des contrôles de qualité pour vérifier le volume, l’unicité, les valeurs manquantes, l’âge à l’embauche et l’assiduité.
  • Secret Mission: construire un Tableau Croisé Dynamique qui compare le taux d’attrition observé entre les catégories de genre.

Y a-t-il des prérequis ?

Les bases d’Excel suffisent pour suivre ce projet. Aucune connaissance préalable du langage M n’est nécessaire.

Before We Start

Avant de commencer, prenez un moment pour définir le résultat que vous visez. Vous allez créer un jeu de données synthétiques RH reproductible pour analyser l’attrition et soutenir les décisions de management.

Prepare the Workbook and Power Query

A reproducible HR dataset needs a stable home before any generation logic is added. A well-named workbook keeps every later output tied to the same file.

This step confirms that Microsoft Excel for Microsoft 365 can run Power Query before you write the dataset logic. The one-row test catches an environment problem while it is still quick to fix.

In this step, get ready to:
  • Save the project workbook as HR_Analytics_Excel.xlsx.
  • Establish the blank HR_Dataset query.
  • Confirm that the Advanced Editor previews a one-row status table.
Set up the workbook

The workbook is the container for every query you build in this project. Saving it first gives the dataset a clear location from the start.

  • Press Cmd+Space on macOS or the Windows key on Windows to open your search bar.
  • Type Excel in the search bar.
  • Select the Excel app from the search results.
  • Choose the blank workbook option on Excel's start screen.
  • Save the blank workbook to your Desktop as HR_Analytics_Excel.xlsx.

That first foundation is in place: your synthetic dataset now has a clearly named workbook on your Desktop.

Create and name the blank query

A blank query gives you an isolated place for the dataset generator. Its name distinguishes the main dataset from the audit query you add later.

  • Select Data in the Excel ribbon.
  • Select Get Data.
  • Select From Other Sources.
  • Select Blank Query.

You should see Power Query Editor with a new blank query ready for a name.

  • Replace the current query name in Power Query Editor's query settings area with HR_Dataset.

The query list now shows HR_Dataset as the active query.

Why use Power Query?

Power Query stores generation logic as an ordered sequence of transformations. Each business rule stays visible for audit.

Random worksheet formulas can change during recalculation. A query creates a cleaner path to reproducible refreshes.

Test the query in Advanced Editor

The Advanced Editor contains the M expression that defines a query's output. A tiny status table proves that the editor can evaluate your code successfully.

  • Select Home in Power Query Editor.
  • Select Advanced Editor.
  • Replace all current content in the Advanced Editor with this expression:
#table({"Status"}, {{"Power Query ready"}})

What does this expression do?

  • The #table expression creates a table directly inside the query.
  • The {"Status"} list defines one column.
  • The {{"Power Query ready"}} list defines one data row.
  • Predict whether the expression creates a one-row table or produces an error.
  • Run the expression by selecting Done.

You should see a preview with one Status column. Its only row contains Power Query ready.

Preview not showing the Status row?

  • Check that the expression contains one brace pair around the column list.
  • Check that the row list uses two nested brace pairs.
  • Select Done after correcting the expression.

help me fix my Power Query status table expression.

✔️ Awesome, I've got everything!

That environment check is complete: the HR_Dataset query now matches this step's checkpoint.

ⓧ I'd like to double check the full code

  • Compare the Advanced Editor content with the complete query below.
#table({"Status"}, {{"Power Query ready"}})

Your workbook has passed its environment check. Next, you will generate the reproducible backbone for 2,000 employee records.

Generate the 2,000-Employee Structure

Le test d’une ligne a confirmé que Power Query exécute correctement votre code. Votre requête ne contient toutefois encore aucune structure employé.

La première victoire visible consiste à générer immédiatement 2 000 employés identifiables. Les nombres auxiliaires n’ont pas encore de signification métier. Ils donnent une base reproductible aux règles RH des prochaines étapes.

In this step, get ready to:
  • Générer un index couvrant 2 000 employés.
  • Créer 14 colonnes aléatoires reproductibles avec des graines fixes.
  • Confirmer que les identifiants vont de EMP-0001 à EMP-2000.
Construire la base reproductible

Une graine fixe permet à List.Random de recréer la même liste à chaque exécution. Vos futurs calculs reposeront ainsi sur les mêmes valeurs auxiliaires après chaque actualisation.

  • Dans l’éditeur avancé déjà ouvert, sélectionnez tout le contenu de la requête HR_Dataset.
  • Remplacez le contenu sélectionné par ce code:
let
    RowCount = 2000,

    Source = Table.FromColumns(
        {
            List.Numbers(1, RowCount),
            List.Random(RowCount, 101), List.Random(RowCount, 103),
            List.Random(RowCount, 107), List.Random(RowCount, 109),
            List.Random(RowCount, 113), List.Random(RowCount, 127),
            List.Random(RowCount, 131), List.Random(RowCount, 137),
            List.Random(RowCount, 139), List.Random(RowCount, 149),
            List.Random(RowCount, 151), List.Random(RowCount, 157),
            List.Random(RowCount, 163), List.Random(RowCount, 167)
        },
        {
            "Index", "rAge", "rGender", "rDepartment", "rRole",
            "rSalary", "rTenure", "rPerformance", "rAttendance",
            "rAttrition", "rLocation", "rEducation", "rTraining",
            "rPromotion", "rSatisfaction"
        }
    ),

    AddEmployeeID = Table.AddColumn(Source, "Employee_ID", each "EMP-" & Text.PadStart(Text.From([Index]), 4, "0"), type text)
in
    AddEmployeeID

Que fait ce code ?

  • La variable RowCount fixe le volume du dataset à 2000 lignes.
  • La fonction List.Numbers crée la séquence utilisée par la colonne Index.
  • Les 14 appels à List.Random produisent les nombres auxiliaires des futures variables RH.
  • La fonction Table.FromColumns aligne les 15 listes pour former une table.
  • La fonction Text.PadStart complète chaque index avec des zéros pour créer un identifiant sur quatre chiffres.
  • La ligne in AddEmployeeID renvoie la table enrichie par la colonne Employee_ID.
  • Sélectionnez Done pour exécuter la requête.

L’aperçu affiche maintenant une table comportant Index. Vous voyez aussi les 14 colonnes auxiliaires décimales. La colonne Employee_ID commence par EMP-0001.

La prévisualisation ne se charge pas ?

Vérifiez que l’ancienne expression #table({"Status"}, {{"Power Query ready"}}) a entièrement disparu. Un morceau de l’ancienne requête empêcherait le nouveau bloc let de s’exécuter.

Confirmez que la dernière ligne contient exactement AddEmployeeID. Vérifiez aussi les virgules placées entre les étapes.

Aidez-moi à corriger ma requête Power Query qui ne prévisualise pas la structure des 2 000 employés.

✔️ Awesome, I've got everything!

Bien joué. Votre requête génère maintenant la structure reproductible qui accueillera les règles RH.

ⓧ I'd like to double check the full code

  • Comparez le contenu complet de HR_Dataset avec cette version:
let
    RowCount = 2000,

    Source = Table.FromColumns(
        {
            List.Numbers(1, RowCount),
            List.Random(RowCount, 101), List.Random(RowCount, 103),
            List.Random(RowCount, 107), List.Random(RowCount, 109),
            List.Random(RowCount, 113), List.Random(RowCount, 127),
            List.Random(RowCount, 131), List.Random(RowCount, 137),
            List.Random(RowCount, 139), List.Random(RowCount, 149),
            List.Random(RowCount, 151), List.Random(RowCount, 157),
            List.Random(RowCount, 163), List.Random(RowCount, 167)
        },
        {
            "Index", "rAge", "rGender", "rDepartment", "rRole",
            "rSalary", "rTenure", "rPerformance", "rAttendance",
            "rAttrition", "rLocation", "rEducation", "rTraining",
            "rPromotion", "rSatisfaction"
        }
    ),

    AddEmployeeID = Table.AddColumn(Source, "Employee_ID", each "EMP-" & Text.PadStart(Text.From([Index]), 4, "0"), type text)
in
    AddEmployeeID
Contrôler les graines fixes

La reproductibilité dépend du deuxième argument de chaque appel à List.Random. Une graine numérique fixe garantit que chaque colonne auxiliaire conserve sa propre séquence.

  • Sélectionnez Home dans le ruban de Power Query Editor.
  • Sélectionnez Advanced Editor pour revoir le code de HR_Dataset.
  • Confirmez que chacun des 14 appels à List.Random possède un deuxième argument numérique fixe.
  • Confirmez que List.Numbers(1, RowCount) commence l’index à 1.
  • Confirmez que la requête se termine par in AddEmployeeID.
  • Sélectionnez Done sans modifier le code.

Les nombres décimaux visibles ont désormais une origine vérifiable. Une actualisation peut reconstruire la même base au lieu de produire un nouveau scénario.

Vérifier les identifiants extrêmes

Les identifiants rendent chaque ligne traçable. Le premier identifiant vérifie le début de la séquence. Le dernier confirme que la logique atteint bien le 2 000e employé.

  • Ouvrez le menu de filtre de la colonne Employee_ID.
  • Saisissez EMP-0001 comme critère.
  • Appliquez le critère de filtre.

Vous voyez une ligne dont Index vaut 1. Son identifiant est EMP-0001.

  • Supprimez la dernière étape de filtre dans le volet des étapes appliquées.

Avant de vérifier l’autre extrémité, quel identifiant attendez-vous pour l’index 2000 ?

  • Ouvrez le menu de filtre de la colonne Employee_ID.
  • Saisissez EMP-2000 comme critère.
  • Appliquez le critère de filtre.

Vous voyez EMP-2000 avec un Index égal à 2000. Cette ligne confirme que la structure couvre la séquence complète des employés.

  • Supprimez la dernière étape de filtre dans le volet des étapes appliquées.
  • Enregistrez HR_Analytics_Excel.xlsx avec Ctrl+S.

C’est une première victoire concrète. Votre requête produit 2 000 identifiants uniques avec des colonnes auxiliaires reproductibles.

La structure est prête. À l’étape suivante, vous donnerez un premier sens métier à l’âge et à l’ancienneté.

Expose the Tenure Inconsistency

Your Power Query backbone is reproducible. The HR_Dataset query already creates 2,000 stable employee identifiers from fixed random seeds.

Tenure still needs a business relationship with age. A naive tenure rule can imply that someone started work before adulthood.

In this step, get ready to:
  • Generate an age for every synthetic employee.
  • Add a deliberately naive age-at-hire check based on tenure.
  • Filter one employee to expose the impossible result.
Add the naive tenure calculations

Age turns tenure from a standalone number into a relationship. This first version deliberately ignores that relationship so you can see the resulting data-quality problem.

  • In the Power Query Editor from the previous step, select Home.
  • Select Advanced Editor.

You’ll see the existing query ending with the AddEmployeeID output step.

  • Scroll to the bottom of the query.
  • Find this current ending:
    AddEmployeeID = Table.AddColumn(Source, "Employee_ID", each "EMP-" & Text.PadStart(Text.From([Index]), 4, "0"), type text)
in
    AddEmployeeID

Why This Ending Matters

The in AddEmployeeID expression makes AddEmployeeID the displayed output of the query. Replacing this tail lets the three new steps become part of that output.

  • Replace the current ending with this expanded version:
    AddEmployeeID = Table.AddColumn(Source, "Employee_ID", each "EMP-" & Text.PadStart(Text.From([Index]), 4, "0"), type text),
    AddAge = Table.AddColumn(AddEmployeeID, "Age", each 22 + Number.RoundDown([rAge] * 39), type number),
    AddNaiveTenure = Table.AddColumn(AddAge, "Naive_Years_at_Company", each if [Index] = 1 then [Age] else Number.RoundDown([rTenure] * 31), type number),
    AddNaiveAgeAtHire = Table.AddColumn(AddNaiveTenure, "Naive_Age_at_Hire", each [Age] - [Naive_Years_at_Company], type number)
in
    AddNaiveAgeAtHire

What Does This Code Do?

  • The AddAge step converts rAge into ages from 22 through 60.
  • The AddNaiveTenure step gives the first row an intentionally extreme tenure equal to its age.
  • The AddNaiveAgeAtHire step subtracts tenure from age to reveal the implied hiring age.
  • The in AddNaiveAgeAtHire expression displays the result after all three calculations.

✔️ Awesome, I've got everything!

Your query now ends with AddNaiveAgeAtHire while retaining the reproducible source from the previous step.

ⓧ I'd like to double check the full code

  • Compare the complete contents of the Advanced Editor with this reference:
let
    RowCount = 2000,
    ReferenceDate = #date(2026, 9, 1),

    Departments = {"Human Resources", "Finance", "Sales", "IT", "Operations", "Marketing"},
    RolesByDepartment = {
        {"HR Assistant", "HR Specialist", "HR Manager"},
        {"Financial Analyst", "Accountant", "Finance Manager"},
        {"Sales Representative", "Account Executive", "Sales Manager"},
        {"IT Support Specialist", "Data Analyst", "Software Engineer", "IT Manager"},
        {"Operations Coordinator", "Operations Analyst", "Operations Manager"},
        {"Marketing Coordinator", "Marketing Specialist", "Marketing Manager"}
    },
    BaseSalaryByDepartment = {
        {38000, 48000, 70000},
        {45000, 52000, 85000},
        {36000, 50000, 80000},
        {40000, 52000, 65000, 90000},
        {38000, 50000, 75000},
        {39000, 52000, 78000}
    },
    Locations = {"Paris", "Lyon", "Marseille", "Toulouse", "Lille"},
    EducationLevels = {"High School", "Bachelor's", "Master's", "Doctorate"},
    EducationBonuses = {0, 3000, 7000, 12000},

    Source = Table.FromColumns(
        {
            List.Numbers(1, RowCount),
            List.Random(RowCount, 101), List.Random(RowCount, 103),
            List.Random(RowCount, 107), List.Random(RowCount, 109),
            List.Random(RowCount, 113), List.Random(RowCount, 127),
            List.Random(RowCount, 131), List.Random(RowCount, 137),
            List.Random(RowCount, 139), List.Random(RowCount, 149),
            List.Random(RowCount, 151), List.Random(RowCount, 157),
            List.Random(RowCount, 163), List.Random(RowCount, 167)
        },
        {
            "Index", "rAge", "rGender", "rDepartment", "rRole",
            "rSalary", "rTenure", "rPerformance", "rAttendance",
            "rAttrition", "rLocation", "rEducation", "rTraining",
            "rPromotion", "rSatisfaction"
        }
    ),

    AddEmployeeID = Table.AddColumn(Source, "Employee_ID", each "EMP-" & Text.PadStart(Text.From([Index]), 4, "0"), type text),
    AddAge = Table.AddColumn(AddEmployeeID, "Age", each 22 + Number.RoundDown([rAge] * 39), type number),
    AddNaiveTenure = Table.AddColumn(AddAge, "Naive_Years_at_Company", each if [Index] = 1 then [Age] else Number.RoundDown([rTenure] * 31), type number),
    AddNaiveAgeAtHire = Table.AddColumn(AddNaiveTenure, "Naive_Age_at_Hire", each [Age] - [Naive_Years_at_Company], type number)
in
    AddNaiveAgeAtHire

What Should Match?

This reference retains all 14 fixed-seed helper columns. Its final output is AddNaiveAgeAtHire.

  • Apply the updated query by selecting Done.

You’ll see Age, Naive_Years_at_Company, and Naive_Age_at_Hire at the right side of the preview. The query still uses the same 2,000-row source.

New Columns Missing?

Return to the Advanced Editor if the three columns do not appear. Confirm that the AddEmployeeID line ends with a comma.

Check that the final expression is in AddNaiveAgeAtHire.

Help me compare my HR_Dataset query with the expected Power Query steps.

Inspect the impossible hiring age

The new fields create a deliberate data-quality test. The first row uses an extreme tenure rule so its consequence becomes easy to inspect.

Before you filter the preview, what hiring age do you expect when an employee’s tenure equals their current age?

  • Open the filter menu in the Employee_ID column header.
  • Filter the column to EMP-0001.
  • Scroll horizontally to the Age column.
  • Compare Age with Naive_Years_at_Company.
  • Read the value in Naive_Age_at_Hire.

You’ll see the same number in Age and Naive_Years_at_Company. You’ll see 0 in Naive_Age_at_Hire.

This Failure Is Intentional

The naive rule sets the first employee’s tenure equal to their age. Subtracting the same value produces an implied hiring age of zero.

This row claims that the employee joined the company at birth. The visible contradiction proves that tenure cannot be generated independently of age.

Cannot Find EMP-0001?

Check that you filtered the Employee_ID column instead of Index. Confirm that the value includes the hyphen plus four digits.

Help me isolate EMP-0001 in the Power Query preview.

  • Return to Excel.
  • Select Save in the title bar to save HR_Analytics_Excel.xlsx.

That is the important catch: your query now turns an abstract data-quality risk into visible evidence. Next, you’ll replace the naive calculation with a tenure rule that respects each employee’s age.

Apply HR Rules and Finalize the Dataset

The EMP-0001 row exposed the weakness in the naive tenure rule. Generating tenure independently from age can place an employee’s hiring date before adulthood.

The completed Power Query pipeline caps tenure according to age. It then derives the final synthetic data from connected business rules.

In this step, get ready to:
  • Replace the naive tenure logic with the complete set of HR rules.
  • Trace the department dependency from job role to base salary.
  • Confirm that the final output contains 2,000 employees across exactly 16 fields.
Replace the naive logic with linked rules

The complete query keeps the fixed random seeds from your current version. A fixed reference date makes every simulated hiring date auditable.

  • In the Power Query Editor from earlier, select Home.
  • Select Advanced Editor.
  • Select the entire contents of HR_Dataset.

The second tab contains the complete replacement for HR_Dataset.

✔️ I have the complete query in place

Your query should start with let. Its final two lines should return FinalColumns.

ⓧ I'd like to double check the full code

  • Copy the complete query below:
let
    RowCount = 2000,
    ReferenceDate = #date(2026, 9, 1),

    Departments = {"Human Resources", "Finance", "Sales", "IT", "Operations", "Marketing"},
    RolesByDepartment = {
        {"HR Assistant", "HR Specialist", "HR Manager"},
        {"Financial Analyst", "Accountant", "Finance Manager"},
        {"Sales Representative", "Account Executive", "Sales Manager"},
        {"IT Support Specialist", "Data Analyst", "Software Engineer", "IT Manager"},
        {"Operations Coordinator", "Operations Analyst", "Operations Manager"},
        {"Marketing Coordinator", "Marketing Specialist", "Marketing Manager"}
    },
    BaseSalaryByDepartment = {
        {38000, 48000, 70000},
        {45000, 52000, 85000},
        {36000, 50000, 80000},
        {40000, 52000, 65000, 90000},
        {38000, 50000, 75000},
        {39000, 52000, 78000}
    },
    Locations = {"Paris", "Lyon", "Marseille", "Toulouse", "Lille"},
    EducationLevels = {"High School", "Bachelor's", "Master's", "Doctorate"},
    EducationBonuses = {0, 3000, 7000, 12000},

    Source = Table.FromColumns(
        {
            List.Numbers(1, RowCount),
            List.Random(RowCount, 101), List.Random(RowCount, 103),
            List.Random(RowCount, 107), List.Random(RowCount, 109),
            List.Random(RowCount, 113), List.Random(RowCount, 127),
            List.Random(RowCount, 131), List.Random(RowCount, 137),
            List.Random(RowCount, 139), List.Random(RowCount, 149),
            List.Random(RowCount, 151), List.Random(RowCount, 157),
            List.Random(RowCount, 163), List.Random(RowCount, 167)
        },
        {
            "Index", "rAge", "rGender", "rDepartment", "rRole",
            "rSalary", "rTenure", "rPerformance", "rAttendance",
            "rAttrition", "rLocation", "rEducation", "rTraining",
            "rPromotion", "rSatisfaction"
        }
    ),

    AddEmployeeID = Table.AddColumn(Source, "Employee_ID", each "EMP-" & Text.PadStart(Text.From([Index]), 4, "0"), type text),
    AddAge = Table.AddColumn(AddEmployeeID, "Age", each 22 + Number.RoundDown([rAge] * 39), type number),
    AddNaiveTenure = Table.AddColumn(AddAge, "Naive_Years_at_Company", each if [Index] = 1 then [Age] else Number.RoundDown([rTenure] * 31), type number),
    AddNaiveAgeAtHire = Table.AddColumn(AddNaiveTenure, "Naive_Age_at_Hire", each [Age] - [Naive_Years_at_Company], type number),
    AddGender = Table.AddColumn(AddNaiveAgeAtHire, "Gender", each if [rGender] < 0.49 then "Female" else if [rGender] < 0.98 then "Male" else "Non-binary", type text),
    AddDepartmentIndex = Table.AddColumn(AddGender, "DepartmentIndex", each if [rDepartment] < 0.10 then 0 else if [rDepartment] < 0.25 then 1 else if [rDepartment] < 0.50 then 2 else if [rDepartment] < 0.70 then 3 else if [rDepartment] < 0.90 then 4 else 5, type number),
    AddDepartment = Table.AddColumn(AddDepartmentIndex, "Department", each Departments{[DepartmentIndex]}, type text),
    AddRoleIndex = Table.AddColumn(AddDepartment, "RoleIndex", each let RoleList = RolesByDepartment{[DepartmentIndex]} in Number.RoundDown([rRole] * List.Count(RoleList)), type number),
    AddJobRole = Table.AddColumn(AddRoleIndex, "Job_Role", each RolesByDepartment{[DepartmentIndex]}{[RoleIndex]}, type text),
    AddBaseSalary = Table.AddColumn(AddJobRole, "BaseSalary", each BaseSalaryByDepartment{[DepartmentIndex]}{[RoleIndex]}, type number),
    AddEducationIndex = Table.AddColumn(AddBaseSalary, "EducationIndex", each if [rEducation] < 0.25 then 0 else if [rEducation] < 0.70 then 1 else if [rEducation] < 0.95 then 2 else 3, type number),
    AddEducation = Table.AddColumn(AddEducationIndex, "Education_Level", each EducationLevels{[EducationIndex]}, type text),
    AddEducationBonus = Table.AddColumn(AddEducation, "EducationBonus", each EducationBonuses{[EducationIndex]}, type number),
    AddMaxTenureMonths = Table.AddColumn(AddEducationBonus, "MaxTenureMonths", each List.Min({([Age] - 18) * 12, 360}), type number),
    AddTenureMonths = Table.AddColumn(AddMaxTenureMonths, "TenureMonths", each Number.RoundDown([rTenure] * ([MaxTenureMonths] + 1)), type number),
    AddHireDate = Table.AddColumn(AddTenureMonths, "Hire_Date", each Date.AddMonths(ReferenceDate, -[TenureMonths]), type date),
    AddYearsAtCompany = Table.AddColumn(AddHireDate, "Years_at_Company", each Number.Round([TenureMonths] / 12, 1), type number),
    AddPerformance = Table.AddColumn(AddYearsAtCompany, "Performance_Rating", each if [rPerformance] < 0.08 then 1 else if [rPerformance] < 0.25 then 2 else if [rPerformance] < 0.70 then 3 else if [rPerformance] < 0.93 then 4 else 5, type number),
    AddSatisfaction = Table.AddColumn(AddPerformance, "Satisfaction_Score", each if [rSatisfaction] < 0.08 then 1 else if [rSatisfaction] < 0.25 then 2 else if [rSatisfaction] < 0.70 then 3 else if [rSatisfaction] < 0.93 then 4 else 5, type number),
    AddAttendance = Table.AddColumn(AddSatisfaction, "Attendance_Rate", each Number.Round(List.Min({1, 0.80 + (0.16 * [rAttendance]) + (0.01 * [Satisfaction_Score])}), 3), type number),
    AddTrainingHours = Table.AddColumn(AddAttendance, "Training_Hours", each Number.RoundDown([rTraining] * 81), type number),
    AddPromotion = Table.AddColumn(AddTrainingHours, "Promotion_Status", each if [Performance_Rating] >= 4 and [Years_at_Company] >= 2 and [rPromotion] < 0.45 then "Promoted" else if [Performance_Rating] = 3 and [Years_at_Company] >= 4 and [rPromotion] < 0.15 then "Promoted" else "Not Promoted", type text),
    AddSalary = Table.AddColumn(AddPromotion, "Salary", each Number.Round((([BaseSalary] + ([Years_at_Company] * 1200) + [EducationBonus]) * (0.92 + (0.16 * [rSalary]))) / 100, 0) * 100, type number),
    AddLocationIndex = Table.AddColumn(AddSalary, "LocationIndex", each Number.RoundDown([rLocation] * List.Count(Locations)), type number),
    AddLocation = Table.AddColumn(AddLocationIndex, "Location", each Locations{[LocationIndex]}, type text),
    AddAttritionProbability = Table.AddColumn(AddLocation, "AttritionProbability", each List.Min({0.55, 0.06 + (if [Satisfaction_Score] <= 2 then 0.18 else if [Satisfaction_Score] = 3 then 0.06 else 0) + (if [Attendance_Rate] < 0.90 then 0.10 else 0) + (if [Promotion_Status] = "Not Promoted" and [Years_at_Company] >= 5 then 0.04 else 0) + (if [Department] = "Sales" then 0.03 else 0)}), type number),
    AddAttrition = Table.AddColumn(AddAttritionProbability, "Attrition", each if [rAttrition] < [AttritionProbability] then "Yes" else "No", type text),
    AddAgeAtHireCheck = Table.AddColumn(AddAttrition, "Age_at_Hire_Check", each [Age] - [Years_at_Company], type number),
    FinalColumns = Table.SelectColumns(AddAgeAtHireCheck, {"Employee_ID", "Age", "Gender", "Department", "Job_Role", "Salary", "Hire_Date", "Years_at_Company", "Performance_Rating", "Attendance_Rate", "Attrition", "Location", "Education_Level", "Training_Hours", "Promotion_Status", "Satisfaction_Score"})
in
    FinalColumns

How is the query structured?

  • The ReferenceDate value anchors every simulated hiring date to 1 September 2026.
  • The Source step creates 2,000 rows from fixed random seeds.
  • The department index selects a matching role list. The role index reaches the corresponding base salary.
  • The MaxTenureMonths step limits tenure to the employee’s adult working years.
  • The AddAttritionProbability step uses satisfaction, attendance, promotion history, tenure, and department. It does not reference Gender.
  • The FinalColumns step keeps only the 16 fields required for analysis.
  • Paste the copied query into HR_Dataset.
  • Select Done.

The preview refreshes with FinalColumns as the last query step. That is the core generation logic complete.

Query not validating?

Check that the previous contents were fully replaced. A leftover in AddNaiveAgeAtHire line can prevent the completed query from validating.

Confirm that every step before FinalColumns ends with a comma. The FinalColumns step is the final expression before in.

Ask for help if needed: help me find the mismatched line in my HR_Dataset query.

Trace the business-rule dependencies

Each derived field builds on an earlier decision. Walking through the query steps makes those dependencies visible before the temporary fields disappear.

  • Select AddDepartment in the query-step list on the right.

The preview now assigns one of the six scenario departments to each employee.

  • Select AddJobRole.

Each role now comes from the list attached to that employee’s department.

  • Select AddBaseSalary.

The base salary now follows the same department-specific role position.

Treat Salary as a Scenario Input

The salary values are annual euro assumptions for this fictional company. They are not labor-market benchmarks.

The final salary combines the role’s base amount with tenure, education, and a small seeded variation. It is rounded to the nearest €100.

  • Select AddMaxTenureMonths.

The maximum is the smaller value between the employee’s adult working months and 360 months.

  • Select AddTenureMonths.
  • Select AddHireDate.

How the age constraint works

The employee’s age determines the largest valid tenure value. The separate 30-year limit caps that value at 360 months.

The query subtracts the valid tenure from ReferenceDate to calculate Hire_Date.

Before you select the control step, what minimum hiring age do you expect the corrected rule to produce for EMP-0001?

  • Select AddAgeAtHireCheck.
  • Inspect the first row for EMP-0001.

You should now see an Age_at_Hire_Check value of at least 18. The invalid value of 0 has been removed by the age-based tenure cap.

  • Select AddAttritionProbability.

The probability responds to satisfaction, attendance, promotion history, tenure, and department. The formula contains no reference to Gender.

  • Select AddAttrition.

The seeded attrition draw converts that probability into Yes or No for every employee.

Verify the final 16-column output

The last query step acts as a controlled projection. It removes random inputs, lookup indexes, and temporary checks from the deliverable.

Before you select the final step, which temporary fields do you expect to disappear from the preview?

  • Select FinalColumns in the query-step list.

You should see exactly 16 final fields arranged in this order:

  • Identity fields: Employee_ID, Age, Gender, and Department.
  • Employment fields: Job_Role, Salary, Hire_Date, and Years_at_Company.
  • Workforce outcome fields: Performance_Rating, Attendance_Rate, Attrition, and Location.
  • Development fields: Education_Level, Training_Hours, Promotion_Status, and Satisfaction_Score.

The query still contains 2,000 rows. The temporary random columns and Age_at_Hire_Check are absent from this final view.

  • Return briefly to the Microsoft Excel workbook from earlier.
  • Save the changes to HR_Analytics_Excel.xlsx.
  • Return to the Power Query Editor.

Your HR dataset now has reproducible identities, linked business rules, and a clean 16-field output. Next, you will load it into Excel and add the documentation and quality checks that make it auditable.

Load, Document, and Audit the Data

Your completed Power Query logic now produces 2,000 synthetic employees across 16 final fields. The business rules are ready for use outside the editor.

A portfolio dataset needs a loaded table that analysts can filter. It also needs documentation plus repeatable proof of data quality.

This step loads the result into Microsoft Excel. You will document every field before building an audit query.

In this step, get ready to:
  • Load HR_Dataset as tbl_HR on the Data_HR worksheet.
  • Document all 16 fields on the Data_Dictionary worksheet.
  • Build HR_QualityChecks to test the dataset.
Load the dataset into Excel

The query preview is an editing view of your generated data. Loading the query creates the worksheet table that analysts can filter or use in later reporting.

  • Select Home in Power Query Editor.
  • Open the Close & Load menu.
  • Select Close & Load.

Excel loads the query output into a new worksheet. You should see a formatted table whose first header is Employee_ID.

  • Double-click the new worksheet tab.
  • Enter Data_HR as the worksheet name.

The worksheet tab should now display Data_HR.

  • Select a cell inside the loaded table.
  • Select Table Design on the Excel ribbon.
  • Enter tbl_HR in the Table Name field.

The table is now named tbl_HR. Its header row should contain the 16 final fields from Employee_ID through Satisfaction_Score.

  • Save HR_Analytics_Excel.xlsx.

Did the query stay in the editor?

Return to the Home tab in Power Query Editor. Use Close & Load to send the query output to a worksheet.

If the table loads with unexpected columns, confirm that HR_Dataset still ends with in FinalColumns.

Help me diagnose why my Power Query output did not load into Excel correctly.

Document the 16 dataset fields

A data dictionary records what each column means. It gives future analysts one place to check types plus expected values before using tbl_HR.

What belongs in the data dictionary?

Use four columns named Field, Type, Business definition, and Expected values.

  • Employee_ID. Type: Text. Business definition: Unique synthetic employee identifier. Expected values: EMP-0001 through EMP-2000.
  • Age. Type: Number. Business definition: Employee age on the reference date. Expected values: 22 through 60.
  • Gender. Type: Text. Business definition: Synthetic gender category. Expected values: Female, Male, or Non-binary.
  • Department. Type: Text. Business definition: Organizational department. Expected values: Human Resources, Finance, Sales, IT, Operations, or Marketing.
  • Job_Role. Type: Text. Business definition: Role compatible with the employee’s department. Expected values: A role from the department-dependent list.
  • Salary. Type: Number. Business definition: Simulated annual gross salary in euros. Expected values: Positive amounts rounded to the nearest 100 euros.
  • Hire_Date. Type: Date. Business definition: Simulated employee hire date. Expected values: Dates on or before 1 September 2026.
  • Years_at_Company. Type: Number. Business definition: Employee tenure in years. Expected values: 0 through 30 with an age at hire of at least 18.
  • Performance_Rating. Type: Number. Business definition: Simulated annual performance rating. Expected values: Integers from 1 through 5.
  • Attendance_Rate. Type: Number. Business definition: Share of expected workdays attended. Expected values: Decimals from 0 through 1.
  • Attrition. Type: Text. Business definition: Employee departure outcome. Expected values: Yes or No.
  • Location. Type: Text. Business definition: Employee’s primary work site. Expected values: Paris, Lyon, Marseille, Toulouse, or Lille.
  • Education_Level. Type: Text. Business definition: Highest simulated education level. Expected values: High School, Bachelor's, Master's, or Doctorate.
  • Training_Hours. Type: Number. Business definition: Annual training hours. Expected values: Integers from 0 through 80.
  • Promotion_Status. Type: Text. Business definition: Simulated recent promotion status. Expected values: Promoted or Not Promoted.
  • Satisfaction_Score. Type: Number. Business definition: Simulated employee satisfaction score. Expected values: Integers from 1 through 5.
  • Create a worksheet using the plus control beside the worksheet tabs.
  • Rename the new worksheet Data_Dictionary.

You should now see Data_Dictionary beside the Data_HR worksheet.

  • Populate the first row with the four dictionary headers from the reference above.

The first row should identify the field name, data type, business meaning, and expected values.

  • Populate the next 16 rows using the field definitions from the reference above.
  • Save HR_Analytics_Excel.xlsx.

You should see one documented row for every field in tbl_HR. The list should begin with Employee_ID and end with Satisfaction_Score.

Build the quality audit query

A reference query uses the output of HR_Dataset as its source. Refreshing the dataset therefore gives the audit a current set of rows to test.

The audit checks volume, identifier quality, age at hire, attendance bounds, and attrition rate. Its status column turns each rule into visible evidence.

  • Right-click HR_Dataset in the queries pane.
  • Select Reference.

A second query should appear with HR_Dataset as its source.

  • Rename the new query HR_QualityChecks in the query name field.
  • Select Home in Power Query Editor.
  • Select Advanced Editor.

The editor should show the short reference query that Power Query created.

  • Replace the entire editor contents by pasting the audit query below:
let
    Source = HR_Dataset,
    RowCount = Table.RowCount(Source),
    UniqueEmployeeIDs = Table.RowCount(Table.Distinct(Table.SelectColumns(Source, {"Employee_ID"}))),
    BlankEmployeeIDs = Table.RowCount(Table.SelectRows(Source, each [Employee_ID] = null or [Employee_ID] = "")),
    WithAgeAtHire = Table.AddColumn(Source, "Age_at_Hire_Check", each [Age] - [Years_at_Company], type number),
    MinimumAgeAtHire = List.Min(Table.Column(WithAgeAtHire, "Age_at_Hire_Check")),
    InvalidAttendance = Table.RowCount(Table.SelectRows(Source, each [Attendance_Rate] < 0 or [Attendance_Rate] > 1)),
    AttritionRate = Number.Round(Table.RowCount(Table.SelectRows(Source, each [Attrition] = "Yes")) / RowCount, 4),
    Output = Table.FromColumns(
        {
            {"Row count", "Unique Employee_ID", "Blank Employee_ID", "Minimum age at hire", "Attendance outside 0-1", "Attrition rate"},
            {RowCount, UniqueEmployeeIDs, BlankEmployeeIDs, MinimumAgeAtHire, InvalidAttendance, AttritionRate},
            {
                if RowCount = 2000 then "PASS" else "FAIL",
                if UniqueEmployeeIDs = RowCount then "PASS" else "FAIL",
                if BlankEmployeeIDs = 0 then "PASS" else "FAIL",
                if MinimumAgeAtHire >= 18 then "PASS" else "FAIL",
                if InvalidAttendance = 0 then "PASS" else "FAIL",
                if AttritionRate >= 0.08 and AttritionRate <= 0.30 then "PASS" else "REVIEW"
            }
        },
        {"Check", "Result", "Status"}
    )
in
    Output

What does this audit query do?

  • Source points the audit at the final output of HR_Dataset.
  • RowCount counts every employee row.
  • UniqueEmployeeIDs counts distinct values in Employee_ID.
  • BlankEmployeeIDs counts missing or empty employee identifiers.
  • WithAgeAtHire creates a temporary age-at-hire value.
  • MinimumAgeAtHire finds the lowest temporary age-at-hire value.
  • InvalidAttendance counts attendance values outside the allowed 0 to 1 range.
  • AttritionRate calculates the share of employees whose attrition value is Yes.
  • Output packages each check into Check, Result, and Status columns.
  • Select Done.
  • Select Home.
  • Open the Close & Load menu.
  • Select Close & Load.

Excel loads the audit output into a new worksheet. You should see the columns Check, Result, and Status.

  • Rename the loaded worksheet Quality_Checks.
  • Save HR_Analytics_Excel.xlsx.

✔️ Awesome, I've got everything!

Great. Your audit query is loaded beside the dataset plus its data dictionary.

ⓧ I'd like to double check the full code

  • Compare HR_QualityChecks with the complete query below:
let
    Source = HR_Dataset,
    RowCount = Table.RowCount(Source),
    UniqueEmployeeIDs = Table.RowCount(Table.Distinct(Table.SelectColumns(Source, {"Employee_ID"}))),
    BlankEmployeeIDs = Table.RowCount(Table.SelectRows(Source, each [Employee_ID] = null or [Employee_ID] = "")),
    WithAgeAtHire = Table.AddColumn(Source, "Age_at_Hire_Check", each [Age] - [Years_at_Company], type number),
    MinimumAgeAtHire = List.Min(Table.Column(WithAgeAtHire, "Age_at_Hire_Check")),
    InvalidAttendance = Table.RowCount(Table.SelectRows(Source, each [Attendance_Rate] < 0 or [Attendance_Rate] > 1)),
    AttritionRate = Number.Round(Table.RowCount(Table.SelectRows(Source, each [Attrition] = "Yes")) / RowCount, 4),
    Output = Table.FromColumns(
        {
            {"Row count", "Unique Employee_ID", "Blank Employee_ID", "Minimum age at hire", "Attendance outside 0-1", "Attrition rate"},
            {RowCount, UniqueEmployeeIDs, BlankEmployeeIDs, MinimumAgeAtHire, InvalidAttendance, AttritionRate},
            {
                if RowCount = 2000 then "PASS" else "FAIL",
                if UniqueEmployeeIDs = RowCount then "PASS" else "FAIL",
                if BlankEmployeeIDs = 0 then "PASS" else "FAIL",
                if MinimumAgeAtHire >= 18 then "PASS" else "FAIL",
                if InvalidAttendance = 0 then "PASS" else "FAIL",
                if AttritionRate >= 0.08 and AttritionRate <= 0.30 then "PASS" else "REVIEW"
            }
        },
        {"Check", "Result", "Status"}
    )
in
    Output
  • Before you refresh, predict which five mandatory checks should return PASS.
  • Select Data on the Excel ribbon.
  • Select Refresh All.

The Quality_Checks table should show PASS for Row count, Unique Employee_ID, Blank Employee_ID, Minimum age at hire, and Attendance outside 0-1.

The Attrition rate row reports PASS or REVIEW against the scenario guardrail.

Seeing a failed mandatory check?

Confirm that HR_QualityChecks starts with Source = HR_Dataset. A different source can test the wrong table.

Compare the audit code with the full-code tab. Pay close attention to Employee_ID, Age, Years_at_Company, and Attendance_Rate.

Help me diagnose a failed mandatory check in HR_QualityChecks.

Strong finish. Your 2,000-row dataset now carries the documentation and quality evidence needed for analysis.

Secret mission

Auditer la neutralité par genre

La règle d’attrition exclut le genre. Le résultat observé mérite tout de même un contrôle avant de tirer des conclusions. Vous allez construire un Tableau Croisé Dynamique qui compare les taux d’attrition entre les catégories de genre.

Clean Up Your Resources

Clean Up Your Resources

Le projet reste entièrement dans Microsoft Excel for Microsoft 365. Il ne crée aucun coût récurrent au-delà de votre licence existante.

Choisissez de conserver votre classeur pour de futures analyses, de fermer votre session pour y revenir plus tard ou de supprimer le fichier.

Ressources utilisées :

  • Le classeur HR_Analytics_Excel.xlsx contient l’ensemble du projet. Il regroupe les feuilles Excel, les requêtes Power Query, la table tbl_HR et le Tableau Croisé Dynamique de neutralité.

Keep everything running

Aucune action n’est nécessaire. Cette option convient si vous prévoyez de poursuivre l’analyse de vos données RH.

  • Conservez HR_Analytics_Excel.xlsx à son emplacement actuel.
  • Rouvrez le classeur depuis Excel lorsque vous souhaitez actualiser HR_Dataset ou réutiliser tbl_HR.
  • Gardez les feuilles Quality_Checks et Fairness_Audit avec le classeur pour préserver les preuves de qualité.

Pause - I'll come back to this later

Cette option arrête votre session Excel tout en conservant le dataset, sa documentation et ses audits.

  • Enregistrez les dernières modifications de HR_Analytics_Excel.xlsx depuis Excel.
  • Fermez le classeur après la fin de l’enregistrement.
  • Fermez Excel pour terminer la session locale.

Votre classeur reste disponible à son emplacement d’enregistrement. Aucun processus continue de fonctionner en arrière-plan.

Delete - I don't want to use this again

La suppression peut sembler radicale. Votre projet tient dans un seul fichier que vous pouvez conserver sans frais récurrents.

  • Fermez HR_Analytics_Excel.xlsx dans Excel.
  • Appuyez sur la touche Windows pour ouvrir la recherche du système.
  • Saisissez HR_Analytics_Excel.xlsx dans la zone de recherche.
  • Ouvrez l’emplacement du fichier depuis le résultat de recherche.
  • Sélectionnez HR_Analytics_Excel.xlsx dans son emplacement d’enregistrement.
  • Appuyez sur la touche Suppr pour supprimer le classeur.
  • Vérifiez que HR_Analytics_Excel.xlsx n’apparaît plus dans son emplacement d’enregistrement.

Vous ne devriez plus voir le classeur dans cet emplacement. Le dataset, les requêtes et les audits locaux sont maintenant supprimés.

Nice Work!

Nice Work!

Bravo ! Vous avez créé dans Excel un dataset RH synthétique de 2 000 employés avec des relations métier vérifiables. Votre classeur est prêt pour la future analyse exploratoire et le dashboard RH.

Vous avez appris à :

  • Générer 2 000 employés reproductibles dans Power Query à partir de graines fixes. Chaque actualisation conserve la même structure synthétique.
  • Transformer une incohérence d’ancienneté volontaire en une règle garantissant un âge d’embauche minimal de 18 ans. Les autres règles relient aussi le département, le poste, le salaire, la performance, la promotion, la satisfaction et l’attrition.
  • Livrer une table tbl_HR de 2 000 lignes avec les 16 champs demandés. Le dictionnaire documente chaque champ. Les cinq contrôles obligatoires affichent PASS dans Quality_Checks.
  • Accomplir la Secret Mission en comparant les taux d’attrition observés entre les catégories de genre avec un Tableau Croisé Dynamique. Votre audit consigne l’écart maximal sans le présenter comme une preuve causale.

Ready to quiz yourself?