L'informatique à votre portée
L'informatique prêt-à-porter

Faites appel à moi pour vos formations
sur les dpts 81, 31, 82, 11 et 67 (Strasbourg)
Confiez moi vos développements Office

FacebookTwitterGoogle BookmarksLinkedin

Power Query

Rechercher la 1ère date d'une série en colonne

11 juillet 2016
Imprimer E-mail

Présentation de la requête

Dernièrement, on m'a demander une requête permettant de visualiser rapidement le 1er jour d'une série d'absences. Le but étant de savoir si les personnes avaient tendance à prendre le vendredi (histoire d'avoir un we un peu plus long) ou le lundi (lendemain de fête difficile).

Les données se présentent ainsi :

MATRICULE Genre Absences
0004DSP M 24/05/2016
0004DSP M 25/05/2016
0004DSP M 26/05/2016
0004DSP M 27/05/2016
0004DSP M 16/06/2016
0004DSP M 17/06/2016
0004DSP M 20/06/2016
0004DSP M 21/06/2016
0006VSC F 23/02/2015
0006VSC F 24/02/2015
0006VSC F 25/02/2015
0006VSC F 31/07/2015
0006VSC F 28/09/2015

Prenons par exemple, le matricule 0004DSP, il nous faut garder uniquement les dates du 24/05/2016 et 16/06/2016. De plus, il me faut compter le nombre de jours d'arrêts, soit 4 jours dans les 2 cas, puisque les samedi et dimanche ne sont pas comptés dans ma source de données.

Pour résumer il nous faut récupérer la 1ère date et le nombre de jours d'une série en colonne.

Création de la requête

Dans Excel 2016, cliquez sur la commande Données > A partir d'un tableau

 Nous devons maintenant modifier notre requête ainsi :

  1. Modifier le type de données, afin que le champ Absences soit de type Date.
  2. Ajouter une colonne personnalisées afin de calculer le n° du jour de la semaine. La fonction est Date.DayOfWeek([Absences],Day.Monday)+1 et le nom du champ JourSem.
  3. Nous devons maintenant calculer le jour précédent l'absence. Etant donné que les jours du Week-end sont à exclure la formule de ce champ nommé JourPrecedent :
    Date.AddDays([Absences],if [jourSem]=1 then -3 else -1)
  4. Nous devons maintenant trier, par ordre croissant, les données sur le matricule puis sur le champ Absences.

Nous avons donc besoin de savoir pour chaque jour d'absence si le champ JourPrecedent est égale ou non à la date d'absence. Si oui, ce n'est pas le 1er jour, sinon c'est le 1er jour. Dans Excel, nous nous ferions une joie (humour) d'écrire de belles formules avec des SI et des ET. Avec Power Query, nous allons simplement utiliser la fusion de requêtes puis le filtre.

Fusion de la requête avec elle-même

 Sur l'onglet Accuel de la fenêtre de Power Query, dans le groupe Combiner, cliquez sur la commande Fusionner des requêtes.

Vous devez maintenant mettre en relation les champs MATRICULE et les champs JourPrecedent et Absences comme indiqué dans l'image ci-après.

 fusion requete

Après validation, vous devez bien entendu développer le nouveau champ, vous pouvez ne conserver que le champ Absence. Renommez-le Fusion.Absences

Developper le champ résultat de la fusion

Vous constatez que votre tableau affiche maintenant la valeur null s'il n'a pas trouvé d'enregistrement correspondant au jour précédent de l'absence. Ces lignes null correspondent donc aux 1ers jours d'absences.

Résultat de la fusion

Etant donné que j'ai également besoin de compter le nombre de jours d'absences contigus, je dois recommencer le même système, mais cette fois avec le jour suivant afin de trouver le dernier jour de la série.

Fusion de la requête sur le jour suivant

Vous devez donc :

  1. Ajouter un champ personnalisé nommé  JourSuivant avec la formule : Date.AddDays([Absences],if [jourSem]=5 then 3 else 1)
  2. Faire la fusion de la table avec elle-même, cette fois, avec une relation entre le JourSuivant et l'Absence.
    Fusion de la requête avec le jour suivant
  3. Développer le champ résulant de la fusion et ne conserver que le champ Absence. Renommez ce champ FusionSuivant.Absences. Vous avez alors un tableau vous permettant de repérer le 1er et le dernier jour de chaque série d'absence.
    Résultat de la fusion avec les jours suivants et précédents

Remplir vers le haut

Il reste une petite chose à résoudre, ces dates de debut et de fin d'absences doivent se retrouver sur le même enregistrement afin d'être exploitable. Nous allons donc ajouter un champ qui va remplacer les valeurs null par la date d'absence et les autres par null. Sur l'onglet Ajouter une colonne, cliquez sur la commande Colonne conditionnelle (c'est une commande récente). Remplissez simplement comme dans l'image suivante.

Ajouter une colonne conditionnelle

Vous pouvez également simplement ajouter une colonne personnalisée, nommée DateFin avec la formule suivante : if [FusionSuivant.Absences]=null then [Absences] else null

Cette nouvelle colonne ne contient donc une date que pour les jours d'absences qui correspond au dernier jour d'une série. Vous devez donc reporter cette date sur chaque données null des enregistrements précédents :

  1. Effectuez un clic-droit sur cette colonne DateFin
  2. Sélectionnez dans le menu contextuel Remplir > Vers le haut

Chaque ligne contient maintenant la date de fin de chaque série d'absence. Il suffit donc de se limiter aux lignes comprenant la 1ère date de la série. Pour cela vous devez filtrer le champ Fusion.Absences sur la valeur null.

Finaliser la requête

Pour finaliser cette requête, vous devez maintenant :

  1. Renommer la colonne Absence en DateDebut.
  2. Ajouter une colonne, NbJours,  calculant le nombre de jour entre DateFin et DateDebut : Duration.Days([DateFin]-[DateDebut])+1
  3. Ajouter une colonne affichant le jour en texte  avec la formule Date.ToText([DateDebut],"dddd","fr-fr")
  4. Supprimer toutes ces colonnes de dates qui nous sont maintenant inutiles.
  5. Modifier le type de données
  6. Charger le résultat de la requête dans un Tableau Excel ou le modèle de données.

Voici le script de la requête :

let
    Source = Excel.CurrentWorkbook(){[Name="t_Absences"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"MATRICULE", type text}, {"Genre", type text}, {"Absences", type date}}),
    JourSemaine = Table.AddColumn(#"Type modifié", "jourSem", each Date.DayOfWeek([Absences],Day.Monday)+1, Int32.Type),
    Ajt_jourPrecedent = Table.AddColumn(JourSemaine, "JourPrecedent", each Date.AddDays([Absences],if [jourSem]=1 then -3 else -1)),
    TrierLignes = Table.Sort(Ajt_jourPrecedent,{{"MATRICULE", Order.Ascending}, {"Absences", Order.Ascending}}),
    Fusion_JourPrecedent = Table.NestedJoin(TrierLignes,{"MATRICULE", "JourPrecedent"},TrierLignes,{"MATRICULE", "Absences"},"Fusion",JoinKind.LeftOuter),
    #"Fusion développé" = Table.ExpandTableColumn(Fusion_JourPrecedent, "Fusion", {"Absences"}, {"Fusion.Absences"}),
    Ajt_JourSuivant = Table.AddColumn(#"Fusion développé", "JourSuivant", each Date.AddDays([Absences],if [jourSem]=5 then 3 else 1)),
    Fusion_JourSuivant = Table.NestedJoin(Ajt_JourSuivant,{"MATRICULE", "JourSuivant"},Ajt_JourSuivant,{"MATRICULE", "Absences"},"FusionSuivant",JoinKind.LeftOuter),
    #"FusionSuivant développé" = Table.ExpandTableColumn(Fusion_JourSuivant, "FusionSuivant", {"Absences"}, {"FusionSuivant.Absences"}),
    Ajt_DateFin = Table.AddColumn(#"FusionSuivant développé", "DateFin", each if [FusionSuivant.Absences]=null then [Absences] else null),
    #"Rempli vers le haut" = Table.FillUp(Ajt_DateFin,{"DateFin"}),
    Filtre_sur_null = Table.SelectRows(#"Rempli vers le haut", each ([Fusion.Absences] = null)),
    SupprColonnesIntermediaires = Table.RemoveColumns(Filtre_sur_null,{"JourPrecedent", "Fusion.Absences", "JourSuivant", "FusionSuivant.Absences"}),
    Renom_Champ_Absences = Table.RenameColumns(SupprColonnesIntermediaires,{{"Absences", "DateDebut"}}),
    Ajt_NbJours_Absences = Table.AddColumn(Renom_Champ_Absences, "NbJours", each Duration.Days([DateFin]-[DateDebut])+1, Int32.Type),
    Ajt_Jour_texte = Table.AddColumn(Ajt_NbJours_Absences, "Jour", each Date.ToText([DateDebut],"dddd","fr-fr"), type text)
in
    Ajt_Jour_texte

Il ne vous reste plus qu'à analyser vos données avec des Tableaux croisés dynamiques et graphiques comme ceci par exemple

Analyse à partir de la requête