// ===================================================================================== // CONTRÔLE QUALITÉ DES DONNÉES RH — REQUÊTES POWER QUERY (M) — Atlas Industries Maroc (fictif) // Classeur hôte : controle-qualite-donnees.xlsx (classeur vide créé par vous) // Excel > Données > Obtenir des données > À partir d'autres sources > Requête vide, // puis Accueil > Éditeur avancé : collez chaque requête ci-dessous et renommez-la EXACTEMENT // avec le nom indiqué (volet Requêtes > clic droit > Renommer). // Ordre de création : 1 à 12. Les requêtes 1 à 11 : « Charger dans… > Créer uniquement la connexion ». // Les requêtes 11 (Anomalies) et 12 (Brouillons) : « Charger dans… > Tableau > Nouvelle feuille ». // ===================================================================================== // ---------- 1. DossierSources (paramètre) ---------------------------------------------- // Chemin local du dossier OneDrive « QualiteDonnees\Sources » synchronisé sur votre poste, terminé par « \ ». // Explorateur de fichiers > OneDrive - Atlas Industries > QualiteDonnees > Sources > clic droit > Copier en tant que chemin d'accès. // Accueil > Gérer les paramètres > Nouveau paramètre : Nom = DossierSources, Type = Texte (ou collez la requête ci-dessous) "C:\Users\votre.nom\OneDrive - Atlas Industries\QualiteDonnees\Sources\" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true] // ---------- 2. DateLimiteTexte (paramètre) --------------------------------------------- "15/01/2027" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true] // ---------- 3. fnChargerTable (fonction) ----------------------------------------------- // Lit un tableau Excel nommé dans un fichier du dossier Sources. (nomFichier as text, nomTable as text) as table => let Contenu = File.Contents(DossierSources & nomFichier), Classeur = Excel.Workbook(Contenu, null, true), Donnees = Classeur{[Item = nomTable, Kind = "Table"]}[Data] in Donnees // ---------- 4. fnPreparerSource (fonction) --------------------------------------------- // Sélectionne les colonnes standard, nettoie (espaces, cellules vides -> null), // ajoute le suffixe de la source et une colonne de présence. (tbl as table, suffixe as text) as table => let Colonnes = {"Matricule", "Nom", "Prénom", "Niveau de poste", "CSP", "Département", "Région", "Site industriel", "Contact responsable"}, Selection = Table.SelectColumns(tbl, Colonnes, MissingField.UseNull), EnTexte = Table.TransformColumnTypes(Selection, List.Transform(Colonnes, (c) => {c, type text})), Nettoye = Table.TransformColumns(EnTexte, List.Transform(Colonnes, (c) => {c, (v) => if v = null then null else let t = Text.Trim(v) in if t = "" then null else t, type nullable text})), SansVide = Table.SelectRows(Nettoye, each [Matricule] <> null), Renomme = Table.RenameColumns(SansVide, { {"Nom", "Nom_" & suffixe}, {"Prénom", "Prenom_" & suffixe}, {"Niveau de poste", "Niveau_" & suffixe}, {"CSP", "CSP_" & suffixe}, {"Département", "Departement_" & suffixe}, {"Région", "Region_" & suffixe}, {"Site industriel", "Site_" & suffixe}, {"Contact responsable", "Contact_" & suffixe}}), Presence = Table.AddColumn(Renomme, "Present_" & suffixe, each true, type logical) in Presence // ---------- 5. SIRH -------------------------------------------------------------------- let Source = fnPreparerSource(fnChargerTable("extraction-sirh.xlsx", "T_SIRH"), "SIRH") in Source // ---------- 6. VP ---------------------------------------------------------------------- let Source = fnPreparerSource(fnChargerTable("fichier-vp.xlsx", "T_VP"), "VP") in Source // ---------- 7. Sites ------------------------------------------------------------------- let Source = fnPreparerSource(fnChargerTable("fichier-sites-industriels.xlsx", "T_Sites"), "Sites") in Source // ---------- 8. Referentiels (4 requêtes courtes, une par tableau) ----------------------- // Regles let Source = fnChargerTable("regles-anomalies.xlsx", "T_Regles") in Source // GrilleCSP let Source = fnChargerTable("regles-anomalies.xlsx", "T_GrilleCSP") in Source // SitesRegions let Source = fnChargerTable("regles-anomalies.xlsx", "T_SitesRegions") in Source // Responsables let Source = fnChargerTable("regles-anomalies.xlsx", "T_Responsables") in Source // ---------- 9. Doublons (règle R09) ----------------------------------------------------- let fnDoublons = (tbl as table, src as text) as table => let Groupe = Table.Group(tbl, {"Matricule"}, { {"Occurrences", each Table.RowCount(_), Int64.Type}, {"E-mail responsable", each List.First(Table.Column(_, "Contact_" & src)), type nullable text}}), Filtre = Table.SelectRows(Groupe, each [Occurrences] > 1), AjoutCode = Table.AddColumn(Filtre, "Code règle", each "R09", type text), AjoutChamp = Table.AddColumn(AjoutCode, "Champ", each "Matricule", type text), AjoutSource = Table.AddColumn(AjoutChamp, "Source à corriger", each src, type text), AjoutValeurs = Table.AddColumn(AjoutSource, "Valeurs constatées", each "Matricule présent " & Text.From([Occurrences]) & " fois dans la source " & src, type text) in Table.SelectColumns(AjoutValeurs, {"Matricule", "Code règle", "Champ", "Source à corriger", "Valeurs constatées", "E-mail responsable"}), Resultat = Table.Combine({fnDoublons(SIRH, "SIRH"), fnDoublons(VP, "VP"), fnDoublons(Sites, "Sites")}) in Resultat // ---------- 10. Comparaison (rapprochement par matricule) ------------------------------- let Ids = Table.Distinct(Table.Combine({ Table.SelectColumns(SIRH, {"Matricule"}), Table.SelectColumns(VP, {"Matricule"}), Table.SelectColumns(Sites, {"Matricule"})})), SIRH_U = Table.Distinct(SIRH, {"Matricule"}), VP_U = Table.Distinct(VP, {"Matricule"}), Sites_U = Table.Distinct(Sites, {"Matricule"}), F1 = Table.NestedJoin(Ids, {"Matricule"}, SIRH_U, {"Matricule"}, "S", JoinKind.LeftOuter), E1 = Table.ExpandTableColumn(F1, "S", List.RemoveItems(Table.ColumnNames(SIRH_U), {"Matricule"})), F2 = Table.NestedJoin(E1, {"Matricule"}, VP_U, {"Matricule"}, "V", JoinKind.LeftOuter), E2 = Table.ExpandTableColumn(F2, "V", List.RemoveItems(Table.ColumnNames(VP_U), {"Matricule"})), F3 = Table.NestedJoin(E2, {"Matricule"}, Sites_U, {"Matricule"}, "T", JoinKind.LeftOuter), E3 = Table.ExpandTableColumn(F3, "T", List.RemoveItems(Table.ColumnNames(Sites_U), {"Matricule"})), Tri = Table.Sort(E3, {{"Matricule", Order.Ascending}}) in Tri // ---------- 11. Anomalies (moteur de règles R01 à R09) --------------------------------- let Src = Comparaison, EmailAdminSIRH = Table.SelectRows(Responsables, each [#"Clé"] = "SIRH"){0}[#"E-mail"], RefRegion = Record.FromList(SitesRegions[#"Région attendue"], SitesRegions[#"Site industriel"]), RefCSP = Record.FromList(GrilleCSP[#"CSP attendue"], GrilleCSP[Niveau]), RefContacts = Record.FromList(Responsables[#"E-mail"], Responsables[#"Clé"]), NomsSources = {"SIRH", "VP", "Sites"}, Val = (v) => if v = null then "(vide)" else v, ContactDe = (r as record, src as text) => if src = "SIRH" then EmailAdminSIRH else let c = Record.Field(r, "Contact_" & src) in if c = null then EmailAdminSIRH else c, // R01, R02, R05, R06 : écarts entre sources (le SIRH fait foi) Comparaisons = {{"R01", "Niveau", "Niveau de poste"}, {"R02", "CSP", "CSP"}, {"R05", "Departement", "Département"}, {"R06", "Site", "Site industriel"}}, fnEcarts = (r as record) as list => List.Combine(List.Transform(Comparaisons, (c) => let s = Record.Field(r, c{1} & "_SIRH"), v = Record.Field(r, c{1} & "_VP"), t = Record.Field(r, c{1} & "_Sites"), nb = List.Count(List.Distinct(List.RemoveNulls({s, v, t}))), aCorriger = if s <> null and v <> null and t <> null and v = t and v <> s then {"SIRH"} else List.RemoveNulls({ if s <> null and v <> null and v <> s then "VP" else null, if s <> null and t <> null and t <> s then "Sites" else null}), valeurs = "SIRH = " & Val(s) & " | VP = " & Val(v) & " | Sites = " & Val(t) in if nb > 1 then List.Transform(aCorriger, (src) => [Code = c{0}, Champ = c{2}, Source = src, Valeurs = valeurs, Email = ContactDe(r, src)]) else {})), // R03 : grille Niveau -> CSP (SIRH uniquement) fnGrille = (r as record) as list => let n = r[Niveau_SIRH], c = r[CSP_SIRH], attendu = if n = null then null else Record.FieldOrDefault(RefCSP, n, null) in if r[Present_SIRH] = true and n <> null and c <> null and attendu <> null and c <> attendu then {[Code = "R03", Champ = "CSP", Source = "SIRH", Valeurs = "Niveau = " & n & " | CSP = " & c & " | CSP attendue = " & attendu, Email = EmailAdminSIRH]} else {}, // R04 : champs obligatoires vides Obligatoires = {{"Nom", "Nom"}, {"Prenom", "Prénom"}, {"Niveau", "Niveau de poste"}, {"CSP", "CSP"}, {"Departement", "Département"}, {"Region", "Région"}, {"Site", "Site industriel"}, {"Contact", "Contact responsable"}}, fnManquants = (r as record) as list => List.Combine(List.Transform(NomsSources, (src) => let manquants = List.Transform(List.Select(Obligatoires, (o) => Record.Field(r, o{0} & "_" & src) = null), (o) => o{1}) in if Record.Field(r, "Present_" & src) = true and List.Count(manquants) > 0 then {[Code = "R04", Champ = Text.Combine(manquants, ", "), Source = src, Valeurs = "Champ(s) vide(s) : " & Text.Combine(manquants, ", "), Email = ContactDe(r, src)]} else {})), // R07 : région incohérente avec le site fnRegion = (r as record) as list => List.Combine(List.Transform(NomsSources, (src) => let site = Record.Field(r, "Site_" & src), reg = Record.Field(r, "Region_" & src), attendu = if site = null then null else Record.FieldOrDefault(RefRegion, site, null) in if site <> null and reg <> null and attendu <> null and reg <> attendu then {[Code = "R07", Champ = "Région", Source = src, Valeurs = "Site = " & site & " | Région = " & reg & " | Région attendue = " & attendu, Email = ContactDe(r, src)]} else {})), // R08 : salarié absent d'une source fnAbsences = (r as record) as list => let site = List.First(List.RemoveNulls({r[Site_SIRH], r[Site_VP], r[Site_Sites]})), dep = List.First(List.RemoveNulls({r[Departement_SIRH], r[Departement_VP], r[Departement_Sites]})), presentes = Text.Combine(List.Select(NomsSources, (x) => Record.Field(r, "Present_" & x) = true), ", "), contactAbsent = (src as text) => if src = "SIRH" then EmailAdminSIRH else if src = "VP" then Record.FieldOrDefault(RefContacts, "VP|" & (if dep = null then "" else dep), EmailAdminSIRH) else Record.FieldOrDefault(RefContacts, "Sites|" & (if site = null then "" else site), EmailAdminSIRH) in List.Combine(List.Transform(NomsSources, (src) => if Record.Field(r, "Present_" & src) <> true then {[Code = "R08", Champ = "Matricule", Source = src, Valeurs = "Salarié absent de la source " & src & " (présent dans : " & presentes & ")", Email = contactAbsent(src)]} else {})), AvecListe = Table.AddColumn(Src, "Liste", each List.Combine({fnEcarts(_), fnGrille(_), fnManquants(_), fnRegion(_), fnAbsences(_)})), Selection = Table.SelectColumns(AvecListe, {"Matricule", "Liste"}), Developpe = Table.ExpandListColumn(Selection, "Liste"), NonVide = Table.SelectRows(Developpe, each [Liste] <> null), Colonnes = Table.ExpandRecordColumn(NonVide, "Liste", {"Code", "Champ", "Source", "Valeurs", "Email"}, {"Code règle", "Champ", "Source à corriger", "Valeurs constatées", "E-mail responsable"}), AvecDoublons = Table.Combine({Colonnes, Doublons}), // identité de référence (SIRH, sinon VP, sinon Sites) Premier = (r as record, champ as text) => List.First(List.RemoveNulls({Record.Field(r, champ & "_SIRH"), Record.Field(r, champ & "_VP"), Record.Field(r, champ & "_Sites")})), Id1 = Table.AddColumn(Src, "Nom complet", each Text.Combine(List.RemoveNulls({Premier(_, "Prenom"), Premier(_, "Nom")}), " "), type text), Id2 = Table.AddColumn(Id1, "Département (réf.)", each Premier(_, "Departement"), type text), Id3 = Table.AddColumn(Id2, "Site (réf.)", each Premier(_, "Site"), type text), Id4 = Table.AddColumn(Id3, "Région (réf.)", each Premier(_, "Region"), type text), Identite = Table.SelectColumns(Id4, {"Matricule", "Nom complet", "Département (réf.)", "Site (réf.)", "Région (réf.)"}), J1 = Table.NestedJoin(AvecDoublons, {"Matricule"}, Identite, {"Matricule"}, "Id", JoinKind.LeftOuter), X1 = Table.ExpandTableColumn(J1, "Id", {"Nom complet", "Département (réf.)", "Site (réf.)", "Région (réf.)"}), J2 = Table.NestedJoin(X1, {"Code règle"}, Regles, {"Code règle"}, "Rg", JoinKind.LeftOuter), X2 = Table.ExpandTableColumn(J2, "Rg", {"Type d'anomalie", "Gravité", "Action corrective"}), J3 = Table.NestedJoin(X2, {"E-mail responsable"}, Responsables, {"E-mail"}, "Rs", JoinKind.LeftOuter), X3 = Table.ExpandTableColumn(J3, "Rs", {"Prénom", "Nom"}, {"Prénom responsable", "Nom responsable"}), Tri = Table.Sort(X3, {{"Matricule", Order.Ascending}, {"Code règle", Order.Ascending}, {"Source à corriger", Order.Ascending}}), Index = Table.AddIndexColumn(Tri, "N°", 1, 1, Int64.Type), AjoutID = Table.AddColumn(Index, "ID anomalie", each "ANO-" & Text.PadStart(Text.From([#"N°"]), 3, "0"), type text), AjoutStatut = Table.AddColumn(AjoutID, "Statut", each "À valider", type text), Final = Table.SelectColumns(AjoutStatut, {"ID anomalie", "Matricule", "Nom complet", "Code règle", "Type d'anomalie", "Gravité", "Champ", "Source à corriger", "Valeurs constatées", "Département (réf.)", "Site (réf.)", "Région (réf.)", "Prénom responsable", "Nom responsable", "E-mail responsable", "Action corrective", "Statut"}) in Final // ---------- 12. Brouillons (un e-mail par responsable et par type d'anomalie) ----------- let Src = Anomalies, Groupe = Table.Group(Src, {"E-mail responsable", "Prénom responsable", "Nom responsable", "Code règle", "Type d'anomalie"}, { {"Nombre", each Table.RowCount(_), Int64.Type}, {"IDs anomalies", each Text.Combine(_[#"ID anomalie"], ", "), type text}, {"Lignes", each Text.Combine(Table.TransformRows(_, (l) => "• " & l[#"ID anomalie"] & " — " & l[Matricule] & " — " & l[#"Nom complet"] & " — source à corriger : " & l[#"Source à corriger"] & " — " & l[#"Valeurs constatées"]), "#(lf)"), type text}}), ReglesMail = Table.SelectColumns(Regles, {"Code règle", "Objet e-mail", "Message type", "Action corrective"}), J = Table.NestedJoin(Groupe, {"Code règle"}, ReglesMail, {"Code règle"}, "R", JoinKind.LeftOuter), X = Table.ExpandTableColumn(J, "R", {"Objet e-mail", "Message type", "Action corrective"}), Tri = Table.Sort(X, {{"Code règle", Order.Ascending}, {"E-mail responsable", Order.Ascending}}), Objet = Table.AddColumn(Tri, "Objet", each [#"Objet e-mail"] & " — " & Text.From([Nombre]) & " dossier(s)", type text), // Corps en texte brut (sauts de ligne #(lf)) : prêt à coller dans Outlook ou à utiliser en publipostage Word Corps = Table.AddColumn(Objet, "Corps", each "Bonjour " & [#"Prénom responsable"] & ",#(lf)#(lf)" & [#"Message type"] & "#(lf)#(lf)" & [Lignes] & "#(lf)#(lf)" & "Action demandée : " & [Action corrective] & "#(lf)" & "Merci d'effectuer la correction avant le " & DateLimiteTexte & " et de répondre à ce message une fois la correction faite.#(lf)#(lf)" & "Cordialement,#(lf)Ilham Bennis#(lf)Responsable Données RH & SIRH — Atlas Industries Maroc", type text), Index = Table.AddIndexColumn(Corps, "N°", 1, 1, Int64.Type), AjoutID = Table.AddColumn(Index, "ID brouillon", each "MAIL-" & Text.PadStart(Text.From([#"N°"]), 3, "0"), type text), Final = Table.SelectColumns(AjoutID, {"ID brouillon", "E-mail responsable", "Prénom responsable", "Nom responsable", "Code règle", "Type d'anomalie", "Nombre", "IDs anomalies", "Objet", "Corps"}) in Final // ===================================================================================== // CONTRÔLES APRÈS ACTUALISATION (Données > Actualiser tout) // - Comparaison : 61 lignes (60 matricules SIRH + AIM-1061 absent du SIRH) // - Anomalies : 18 lignes, identiques à l'onglet « Anomalies attendues » de resultat-attendu-anomalies.xlsx // - Brouillons : 15 lignes (MAIL-001 à MAIL-015) // Nommez les tableaux de sortie « Anomalies » et « Brouillons » (Création de tableau > Nom du tableau) : // Copiez ensuite le tableau Brouillons (Copier > Collage spécial > Valeurs) dans l'onglet « Brouillons à valider » // de suivi-validation-envois.xlsx : la validation humaine et l'envoi se font à partir de cet onglet. // =====================================================================================