ODAT :: Outillage sur les dates en PowerQuery

[oguruma]

Avant toutes choses tous mes Voeux pour 2024.
En guise... d'étrennes qui sait, un petit outillage sur les dates.
Nouvelle année ==> certainement des questions sur les dates ?
Ceci est un simple outillage qui permet de générer des listes de dates, mois, semaines, et trimestres.
Il montre également qu'il n'y pas de solution unique mais plusieurs.
Le fait de passer par PowerQuery vous évitera de charger vos feuilles en calculs.

Pour intervenir sur le paramétrage des dates à calculer onglet [Params]

Quelques exemples

let
    //---------------------------------------------------------------------------------------------------------
    // Génération d'une séquence de dates avec des valeurs fixes
    //---------------------------------------------------------------------------------------------------------
    DebAnnee=2024,
    DebMois=1,
    DebJour=1,
    Duree=1,
    Nbr=31,

    //---------------------------------------------------------------------------------------------------------
    // Construction de la liste
    //---------------------------------------------------------------------------------------------------------
    LstDates= List.Dates(                 
        #date( DebAnnee, DebMois, DebJour ),   
        Nbr,                       
        #duration( Duree, 0, 0, 0 )
        ),
    
    //------------------------------------------------------------------------------------------------------
    // On aurait pu garder la liste et la présenter aisni dans Excel mais autant la formater en table.
    // Ca nous permet de gérer le type date au retour et d'imposer un nom de colonne
    // ----------------------------------------------------------------------------------------------------
    ToTable=Table.FromList(LstDates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    ToTableTypeDate = Table.TransformColumnTypes(ToTable,{{"Date", type date}})
in
    ToTableTypeDate

let
    Nbr=getParameters("TB_PARAMS","NOMBRE_SEQUENCES"),
    Duree=getParameters("TB_PARAMS","DUREE"),


    //---------------------------------------------------------------------------------------------------------
    // Construction de la liste
    //---------------------------------------------------------------------------------------------------------
    LstDates= List.Dates(                 
        StartYYMMDD,   
        Nbr,                       
        #duration( Duree, 0, 0, 0 )
        ),

    //---------------------------------------------------------------------------------------------------------
    //ToTable=Table.FromList(LstDates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    // 2ème syntaxe qui permet de préciser le nom de la colonne et son type
    //---------------------------------------------------------------------------------------------------------

    ToTable=Table.FromList(LstDates, Splitter.SplitByNothing(), type table[ Date = Date.Type ])
 
    // Et là plus besoin de cette étape
    // ToTableTypeDate = Table.TransformColumnTypes(ToTable,{{"Date", type date}})
in
    ToTable

let

    Duree=1,
    //---------------------------------------------------------------------------------------------------------------
    // A partir d'une date de début et de fin très simple de déterminer l'étendue de calendrier en nombre de jours
    //---------------------------------------------------------------------------------------------------------------

    //---------------------------------------------------------------------------------------------------------
    // On récupère les paramètres construits StartYYMMDD et EndYYMMDD
    //---------------------------------------------------------------------------------------------------------
    LstDates= List.Dates(                  
        StartYYMMDD,    
        Duration.Days( EndYYMMDD - StartYYMMDD ) + 1,                        
        #duration( Duree, 0, 0, 0 )
        ),

    //---------------------------------------------------------------------------------------------------------
    // Construction de la table de dates
    //---------------------------------------------------------------------------------------------------------
    ToTable=Table.FromList(LstDates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    ToTableTypeDate = Table.TransformColumnTypes(ToTable,{{"Date", type date}})
in
    ToTableTypeDate

let
    //---------------------------------------------------------------------------------------------------------
    // Construction de la liste de date sur la base d'une liste
    // borne de départ et borne d'arrivée
    //---------------------------------------------------------------------------------------------------------
    LstNbrDates = { Number.From( StartYYMMDD ) .. Number.From( EndYYMMDD ) },

    //---------------------------------------------------------------------------------------------------------
    // Construction de la table
    //---------------------------------------------------------------------------------------------------------
    ToTable=Table.FromList(LstNbrDates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    ToTableTypeDate = Table.TransformColumnTypes(ToTable,{{"Date", type date}})
in
    ToTableTypeDate

Voir le fichier joint pour plus d'exemples visuels

Concernant les mois

let
    //---------------------------------------------------------------------------------------------------------
    // Variante et affichage des noms de mois uniquement
    //---------------------------------------------------------------------------------------------------------
    ListDates = List.Generate( () => 
                 StartYYMMDD,
                 each _ <= EndYYMMDD,
                 each Date.AddMonths( _, 1 ) ),  // On passe au mois suivant

    //---------------------------------------------------------------------------------------------------------
    // Table des dates
    //---------------------------------------------------------------------------------------------------------
    ToTableDate = Table.FromList(ListDates, Splitter.SplitByNothing(), type table[Date = Date.Type], null, ExtraValues.Error),

    //---------------------------------------------------------------------------------------------------------
    // Table en mois
    //---------------------------------------------------------------------------------------------------------
    ToTableMois = Table.AddColumn(ToTableDate, "Mois", each Text.Proper(Date.MonthName([Date], "FR-fr"))),
    ToTableMois_Num = Table.AddColumn(ToTableMois, "Num Mois", each Date.Month([Date]), Int64.Type),
    ToTableMois_NumYYYY = Table.AddColumn(ToTableMois_Num, "Mois-Annee", each Date.ToText([Date],"MMMM-yyyy"), type text),
    ToTableMois_NumAlpha = Table.AddColumn(ToTableMois_NumYYYY, "Mois-Alpha", each Date.ToText([Date],"MM"), type text)
in
    ToTableMois_NumAlpha

Concernant les semaines

let
    ListDates = List.Generate( () => 
                 StartYYMMDD,
                 each _ <= EndYYMMDD,
                 each Date.AddWeeks( _, 1 ) ), // On ajoute une semaine

    //---------------------------------------------------------------------------------------------------------
    // Table des dates
    //---------------------------------------------------------------------------------------------------------
    ToTableDate = Table.FromList(ListDates, Splitter.SplitByNothing(), type table[Date = Date.Type], null, ExtraValues.Error),

    //---------------------------------------------------------------------------------------------------------
    // Table des semaines
    // Appel d'une fonction spécifique pour calculer la semaine ISO
    //---------------------------------------------------------------------------------------------------------
    ToTableSem = Table.AddColumn(ToTableDate, "Num Semaine", each NumberOfWeekISO([Date])),
    ToTableSemAnnee = Table.AddColumn(ToTableSem, "Annee Num Semaine", each Number.ToText(Date.Year([Date])) & "-" & Text.PadStart(Number.ToText(NumberOfWeekISO([Date])),2,"0"))
in
    ToTableSemAnnee

Concernant les trimestres

let
    ListDates = List.Generate( () => 
                 StartYYMMDD,
                 each _ <= EndYYMMDD,
                 each Date.AddQuarters( _, 1 ) ), // On ajoute un trimestre

    //---------------------------------------------------------------------------------------------------------
    // Table des dates
    //---------------------------------------------------------------------------------------------------------
    ToTableDate = Table.FromList(ListDates, Splitter.SplitByNothing(), type table[Date = Date.Type], null, ExtraValues.Error),

    //---------------------------------------------------------------------------------------------------------
    // Table des trimestres
    //---------------------------------------------------------------------------------------------------------
    ToTableTrim = Table.AddColumn(ToTableDate, "Mois", each "Trim-" & Text.From(Date.QuarterOfYear([Date])))
in
    ToTableTrim

Et pour terminer une fonction pour calculer le n° de semaine exact afin de bien retomber en fin d'année avec les bons n° de semaine qui se calculent de jeudi en jeudi.
Bon... y a peut-être mieux mais en tous elle fonctionne je suppose.... où il a fallu jongler avec le complément en jours pour terminer la semaine avec l'arrondi d'une division par 7 (jours par semaine) - Test effectué sur 2023 et 2024 uniquement. A vérifier sur d'autres années.

//*******************************************************************************************
// Calcul du n° de semaine ISO - Calendrier Grégorien
//*******************************************************************************************

(pDate as date) as number =>
let
    //---------------------------------------------------------------------------------
    // Calcul de l'écart du jeudi entre deux semaines, soit l'actuelle et la suivante
    // et on tente de compléter sur les 3 jours manquant pour finir la semaine
    //---------------------------------------------------------------------------------
    EcartJeudi = Date.AddDays(pDate, -1 * Date.DayOfWeek(pDate, Day.Monday) + 3),

    //---------------------------------------------------------------------------------
    // Arrondi sur les 7 jours de la semaine par rapport à l'écart calculé
    //---------------------------------------------------------------------------------
    NumeroSem = Number.RoundUp(Date.DayOfYear(EcartJeudi) / 7)
in
    NumeroSem

Les requêtes de paramétrage
StartYYMMDD

let
    DateDeb=getParameters("TB_PARAMS","DATE_DEB"),
    Day=Date.Day(DateDeb),
    Month=Date.Month(DateDeb),
    Year=Date.Year(DateDeb),
    Param=#date(Year, Month, Day) meta [IsParameterQuery=true, Type="Date", IsParameterQueryRequired=true]
in
    Param

EndYYMMDD

let
    DateFin=getParameters("TB_PARAMS","DATE_FIN"),
    Day=Date.Day(DateFin),
    Month=Date.Month(DateFin),
    Year=Date.Year(DateFin),
    Param=#date(Year, Month, Day) meta [IsParameterQuery=true, Type="Date", IsParameterQueryRequired=true]
in
    Param

getParameters

(pTable as text, pName as text) =>
 let
        Source = Excel.CurrentWorkbook(){[Name=pTable]}[Content],
        RowsParams = Table.SelectRows(Source, each ([PARAMETRE] = pName)),
        value = RowsParams{0}[VALEUR]
in
        value

Quelques exemples de résultats en image

Et un début de calendrier simplifié