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

Une Table de temps

18 décembre 2014
Imprimer E-mail

Avec Excel 2013 ou depuis la version 2010 avec Power Pivot, nous pouvons mettre 2 tables en relation. Nous pouvons donc mettre en relation un tableau Excel contenant un champ date avec un tableau de dates afin d'avoir quasi instantanément des infos sur l'année, le mois, le jour, la semaine, ...

Création de la table de temps

 Je pourrais vous donner le script de la fonction, mais ce sera plus pédagogique de voir comment le créer. Le but étant de créer une liste de dates entre 2 dates définies, commençons donc par créer un tableau, nommé Exemple, comprenant uniquement une date de début et une date de fin.

Power Query - Tableau source

Puis créons notre requête Power Query :

  • onglet POWER QUERY ;
  • clic sur A partir du tableau.

Ajouter une colonne personnalisée afin de calculer le nombre de jours entre le début et la fin :

  • onglet Ajouter une colonne > Ajouter une colonne personnalisée ;
  • saisissez la formule : [Date Fin]-[Date Debut] ;
  • renommez le champ Duree.

Si vous utilisez une version française de Power Query, celui-ci renomme les étapes avec des mots français, des espaces et des accents. Il peut être pratique de renommer chacune de vos étapes :

  • clic-droit sur l'étape dans le volet de droite ;
  • saisissez un nom en évitant les espaces et accents.

Power Query - renommer étape

Afin que la création de toute les dates entre la 1ère et la dernière s'effectue correctement, il est important de préciser le type de données de chacune des colonnes : Date pour les dates de début et de fin et Nombre entier pour la durée :

  • clic-droit sur 1 ou plusieurs champs ;
  • Modifier le type > choisir le type adapté dans la liste.

Power Query - Modifier le type de données d'une colonne

  • Renommer cette étape avec le nom TypeDuree

Création d'une liste de dates

 Pour créer une liste de dates à partir de ces 3 champs, nous ne pouvons pas utiliser une commande sur les onglets de l'interface de Power Query. Nous devons donc :

  • Cliquer sur fx de la barre de formule afin d'ajouter une étape ;
  • Saisissez cette formule dans la barre de formule :
    = List.Dates(TypeDuree[Date Debut]{0}, TypeDuree[Duree]{0}+1, #duration(1, 0, 0, 0)),
  • Validez cette étape avec la touche Entrée ou la coche de la barre de formule ;
  • Renommez l'étape ListeDates

Nous avons maintenant une colonne contenant l'ensemble des dates situées entre les dates de début et de fin.
List.Dates permet de créer une liste de dates :

  • depuis une date de début : TypeDuree[Date Debut]{0} récupère la 1ère ligne {0} du champ [Date Debut] de l'étape TypeDuree ;
  • en indiquant le nombre de dates à retourner, ici il est important d'ajouter 1 afin d'avoir la date de fin ;
  • et un type d'increment, ce dernier une valeur Duration. Une valeur duration stocke une représentation de la durée entre deux points sur une ligne de temps dont l'unité de mesure est 100 nanosecondes. Les valeurs de durée peuvent être construites à l'aide de la fonction #duration(d,h,m,s).
    d représente le nombre de jours, h le nombre d'heures, m le nombre de minutes et s le nombre de secondes.

 Pour continuer à travailler, il faut maintenant convertir cette liste de dates en une table de dates :

  • onglet Convertir | Liste ;
  • clic sur A la table.
    Power Query - convertir une liste en table
  • clic sur Ok
     Power Query - Convertir une liste en table
  • Renommer le nom du champ en remplaçant null par {"Date Ref"} dans la barre de formule :
    Power Query - Renommer une colonne de la conversion

Ajout d'infos sur les dates

Il est temps maintenant d'ajouter d'autres colonnes pour décomposer nos dates. Utilisez les commandes située dans le menu Date de l'onglet Ajouter une colonne.

Power Query - colonnes personnalisée sur les dates

Il vous faudra reprendre la fonction sur le n° du jour dans la semaine. En effet, il vous faut préciser que la semaine commence le lundi et y ajouter 1 car cette fonction renvoie un n° de 0 à 6 :

each Date.DayOfWeek([Date Ref],Day.Monday)+1

De plus la fonction renvoyant le n° de la semaine ne renvoie pas un numéro correspondant à la norme ISO 8601, mais considère que la semaine 1 est la semaine comprenant le 1er janvier.

Vous pouvez également ajouter d'autres infos tel que :

  • Date.IsInPreviousDay : la date est la date d'hier ;
  • Date.IsInCurrentDay : la date est la date du jour ;
  • Date.IsInNextDay : la date est la date de demain ;
  • Date.IsInPreviousWeek : la date se situe dans la semaine précédente ;
  • Date.IsInCurrentWeek : la date se situe dans la semaine courante ;
  • Date.IsInNextWeek : la date se situe dans la semaine prochaine ;
  • Date.IsInPreviousMonth : la date se situe le mois précédent ;
  • Date.IsInCurrentMonth : la date se situe le mois courant ;
  • Date.IsInNextMonth : la date se situe le mois prochain ;
  • Date.IsInPreviousQuarter : la date se situe le trimeste précédent ;
  • Date.IsInCurrentQuarter : la date se situe le trimeste courant ;
  • Date.IsInNextQuarter : la date se situe le trimeste prochain ;
  • Date.IsInPreviousYear : la date se situe l'année précédente ;
  • Date.IsInCurrentYear : la date se situe l'année courante ;
  • Date.IsInNextYear : la date se situe l'année prochaine ;
  • Date.IsInYearToDate : la date se situe cette année entre le 1er janvier et aujourd'hui ;
  • Date.IsLeapYear: la date se situe dans une année bissextile.

Pour ce faire, ajoutez une colonne personnalisée et utilisez ces fonctions comme dans l'image ci-desssous.

Power Query - colonne personnalisée

Infos en texte sur les date

 Afin de permettre l'ajout des jours et mois en texte en français et en anglais, je n'utilise pas la fonction . Je déclare d'abord 4 listes en passant en affichage avancé :

    ListJour = {"Lundi","Mardi","Mercredi","Jeudi","Vendredi","Samedi","Dimanche"},
    ListMois = {"Janvier","Février","Mars","Avril","Mai","Juin","Juillet","Août","Septembre","Octobre","Novembre","Décembre"},
    ListDay = {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"},
    ListMonth = {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}

 Puis il suffit d'ajouter une colonne avec les formules suivantes :

  • ListMois{[No Mois]-1}
  • ListMonth{[No Mois]-1}
  • ListJour{[No jour Semaine]-1}
  • ListDay{[No jour Semaine]-1}

Il faut soustraire 1 car une liste commence à l'index 0.

Script complet

let
    Source = Excel.CurrentWorkbook(){[Name="Exemple"]}[Content],
    AjoutDuree = Table.AddColumn(Source, "Duree", each [Date Fin]-[Date Debut]),
    TypeDuree = Table.TransformColumnTypes(AjoutDuree,{{"Date Debut", type date}, {"Date Fin", type date}, {"Duree", Int64.Type}}),
    ListeDates = List.Dates(TypeDuree[Date Debut]{0}, TypeDuree[Duree]{0}+1, #duration(1, 0, 0, 0)),
    TableDates = Table.FromList(ListeDates, Splitter.SplitByNothing(), {"Date Ref"}, null, ExtraValues.Error),
    AjoutAnnee = Table.AddColumn(TableDates, "Année", each Date.Year([Date Ref]), type number),
    AjoutNoMois = Table.AddColumn(AjoutAnnee, "No Mois", each Date.Month([Date Ref]), type number),
    AjoutNoJour = Table.AddColumn(AjoutNoMois, "No Jour", each Date.Day([Date Ref]), type number),
    AjoutNoTrim = Table.AddColumn(AjoutNoJour, "No Trimestre", each Date.QuarterOfYear([Date Ref]), type number),
    AjoutNoSem = Table.AddColumn(AjoutNoTrim, "No Semaine", each Date.WeekOfYear([Date Ref]), type number),
    DebutSem = Table.AddColumn(AjoutNoSem, "Debut semaine", each Date.StartOfWeek([Date Ref]), type anynonnull),
    FinSem = Table.AddColumn(DebutSem, "Fin Semaine", each Date.EndOfWeek([Date Ref]), type anynonnull),
    DebutMois = Table.AddColumn(FinSem, "Debut Mois", each Date.StartOfMonth([Date Ref]), type anynonnull),
    FinMois = Table.AddColumn(DebutMois, "Fin Mois", each Date.EndOfMonth([Date Ref]), type anynonnull),
    NbJoursMois = Table.AddColumn(FinMois, "Nb jours mois", each Date.DaysInMonth([Date Ref]), type number),
    NoJourSem = Table.AddColumn(NbJoursMois, "No jour Semaine", each Date.DayOfWeek([Date Ref],Day.Monday)+1, type number),
    NoJourAnnee = Table.AddColumn(NoJourSem, "No jour Année", each Date.DayOfYear([Date Ref]), type number),
    NbSemMois = Table.AddColumn(NoJourAnnee, "Nb semaine Mois", each Date.WeekOfMonth([Date Ref]), type number),
    Hier = Table.AddColumn(NbSemMois, "Hier", each Date.IsInPreviousDay([Date Ref])),
    Aujourdhui = Table.AddColumn(Hier, "Aujourdhui", each Date.IsInCurrentDay([Date Ref])),
    demain = Table.AddColumn(Aujourdhui, "Demain", each Date.IsInNextDay([Date Ref])),
    SemPreced = Table.AddColumn(demain, "Semaine precedente", each Date.IsInPreviousWeek([Date Ref])),
    SemActu = Table.AddColumn(SemPreced, "Semaine actuelle", each Date.IsInCurrentWeek([Date Ref])),
    SemSuivant = Table.AddColumn(SemActu, "Semaine suivante", each Date.IsInNextWeek([Date Ref])),
    MoisPreced = Table.AddColumn(SemSuivant, "Mois precedent", each Date.IsInPreviousMonth([Date Ref])),
    MoisActu = Table.AddColumn(MoisPreced, "Mois actu", each Date.IsInCurrentMonth([Date Ref])),
    MoisSuivant = Table.AddColumn(MoisActu, "Mois suivant", each Date.IsInNextMonth([Date Ref])),
    TrimPreced = Table.AddColumn(MoisSuivant, "Trimestre precedent", each Date.IsInPreviousQuarter([Date Ref])),
    TrimActu = Table.AddColumn(TrimPreced, "Trimestre actu", each Date.IsInCurrentQuarter([Date Ref])),
    TrimSuivant = Table.AddColumn(TrimActu, "Trimestre suivant", each Date.Date.IsInNextQuarter([Date Ref])),
    AnneePreced = Table.AddColumn(TrimSuivant, "Annee precedente", each Date.IsInPreviousYear([Date Ref])),
    AnneeActu = Table.AddColumn(AnneePreced, "Annee en cours", each Date.IsInCurrentYear([Date Ref])),
    AnneeSuivant = Table.AddColumn(AnneeActu, "Annee prochaine", each Date.IsInNextYear([Date Ref])),
    AnAceJour = Table.AddColumn(AnneeSuivant, "Annee A ce jour", each Date.IsInYearToDate([Date Ref])),
    Bissextile = Table.AddColumn(AnAceJour, "Bissextile", each Date.IsLeapYear([Date Ref])),
    ListJour = {"Lundi","Mardi","Mercredi","Jeudi","Vendredi","Samedi","Dimanche"},
    ListMois = {"Janvier","Février","Mars","Avril","Mai","Juin","Juillet","Août","Septembre","Octobre","Novembre","Décembre"},
    ListDay = {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"},
    ListMonth = {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"},
    AddTrimFr = Table.AddColumn(Bissextile, "Trimestre", each "Trim "&Number.ToText([No Trimestre],"0")),
    AddMoisFr = Table.AddColumn(AddTrimFr, "Mois", each ListMois{[No Mois]-1}),
    AddSemFr = Table.AddColumn(AddMoisFr, "Semaine", each "S "&Number.ToText([No Semaine],"0")),
    AddJourFr = Table.AddColumn(AddSemFr, "Jour", each ListJour{[No jour Semaine]-1}),
    AddTrimEn = Table.AddColumn(AddJourFr, "Quarter", each "Quarter "&Number.ToText([No Trimestre],"0")),
    AddMoisEn = Table.AddColumn(AddTrimEn, "Month", each ListMonth{[No Mois]-1}),
    AddJourEn = Table.AddColumn(AddMoisEn, "Day", each ListDay{[No jour Semaine]-1})    
in
    AddJourEn