Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

ODAT :: Outillage sur les dates en PowerQuery

oguruma

XLDnaute Occasionnel
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

PowerQuery: 
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


PowerQuery: 
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


PowerQuery: 
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

PowerQuery: 
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
PowerQuery: 
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

PowerQuery: 
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

PowerQuery: 
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.

PowerQuery: 
//*******************************************************************************************
// 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
PowerQuery: 
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
PowerQuery: 
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
PowerQuery: 
(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é
 

Pièces jointes

  • 1704138924250.png
    6 KB · Affichages: 6
  • ODAT_OutilDate_V0.042.xlsx
    198.2 KB · Affichages: 11
Dernière édition:

oguruma

XLDnaute Occasionnel
Bonjour, voici une évolution. Certaines requêtes de la version originales ont été transformées en fonctions avec quelques petites subtilités supplémentaires sur la manière de développer en M.

fnListDatesBetweenV10
PowerQuery: 
let fnListDatesBetweenV10 =
    (
          pStartYear as number,
          optional pStartMonth as number,
          optional pStartDay as number,
          optional pDuration as number,
          optional pNbr as number,
          optional pToTable as logical,
          optional pDateColumnName as text
    ) =>

        let

            //***********************************************************************************************************
            // Gestion des paramètres
            //***********************************************************************************************************
            DebAnnee=pStartYear,
            DebMois=if pStartMonth is null then 1 else pStartMonth,
            DebJour=if pStartDay is null then 1 else pStartDay,
            Duree=if pDuration is null then 1 else pDuration,
            Nbr=if pNbr is null then 31 else pNbr,
            bToTable=if pToTable is null  then false else pToTable,
            DateColumnName=if pDateColumnName is null then "DATE" else pDateColumnName,

            //---------------------------------------------------------------------------------------------------------
            // Construction de la liste
            //---------------------------------------------------------------------------------------------------------
            ListDates= List.Dates(                     
                #date( DebAnnee, DebMois, DebJour ),       
                Nbr,                           
                #duration( Duree, 0, 0, 0 )
                ),
                
            //---------------------------------------------------------------------------------------------------------
            // Conversion en table
            //---------------------------------------------------------------------------------------------------------
            ToTableTypeDate= if bToTable
                        then 
                            let 
                                t_ToTable=Table.FromList(ListDates, Splitter.SplitByNothing(), {DateColumnName}, null, ExtraValues.Error),
                                t_ToTableTypeDate = Table.TransformColumnTypes(t_ToTable,{{DateColumnName, type date}})
                            in 
                                t_ToTableTypeDate
                        else
                                ListDates
            
        in
            ToTableTypeDate
in
    fnListDatesBetweenV10

Exemple d'utilisation : = fnListDatesBetweenV10(2024,2,15,null ,10,true,"Dates")

fnListDatesBetweenV11
PowerQuery: 
let  fnListDatesBetweenV11 =
    (
          pStartDate as any,
          pNbr as number,
          pDuration as number,
          optional pToTable as logical,
          optional pDateColumnName as text
    ) =>

      let
        //***********************************************************************************************
        // Gestion des paramètres
        // On converti la chaine date en type date
        //***********************************************************************************************
        StartDate = Date.From(pStartDate),
        Nbr = pNbr,
        Duree=pDuration,
        bToTable=if pToTable is null  then false else pToTable,
        DateColumnName=if pDateColumnName is null then "DATE" else pDateColumnName,


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

            ToTable=if bToTable
                    then
                        let
                            StrEval= "type table[ " & DateColumnName & " = Date.Type ]",
                            Eval=Expression.Evaluate(StrEval,[Date.Type=Date.Type]), 
                            t_ToTable=Table.FromList(ListDates, Splitter.SplitByNothing(), {DateColumnName}, Eval, ExtraValues.Error)
                        in 
                            t_ToTable
                    else ListDates
            
        in
            ToTable
in 
    fnListDatesBetweenV11

Exemple d'utilisation : = fnListDatesBetweenV11(StartYYMMDD,Nbr,Duree,true,"Dates" )

Les petites subtilités se trouvent par exemple ici :
PowerQuery: 
StrEval= "type table[ " & DateColumnName & " = Date.Type ]",
Eval=Expression.Evaluate(StrEval,[Date.Type=Date.Type]), 
t_ToTable=Table.FromList(ListDates, Splitter.SplitByNothing(), {DateColumnName}, Eval, ExtraValues.Error)

Cette ligne de code : t_ToTable=Table.FromList(ListDates, Splitter.SplitByNothing(), {DateColumnName}, Eval, ExtraValues.Error) ==> est totalement dynamique et en une seule opération on renomme la colonne et on déclare son type

fnListDatesBetweenV12
PowerQuery: 
let  fnListDatesBetweenV12 =
    (
          pStartDate as any,
          pEndDate as any,
          optional pDuration as number,
          optional pToTable as logical,
          optional pDateColumnName as text
    ) =>

      let
        //***********************************************************************************************
        // Gestion des paramètres
        // On converti la chaine date en type date
        //***********************************************************************************************
        StartDate = Date.From(pStartDate),
        EndDate = Date.From(pEndDate),
        Duree=if pDuration is null then 1 else pDuration,
        bToTable=if pToTable is null  then false else pToTable,
        DateColumnName=if pDateColumnName is null then "DATE" else pDateColumnName,


            //---------------------------------------------------------------------------------------------------------
            // Construction de la liste
            //---------------------------------------------------------------------------------------------------------
            ListDates= List.Dates(                     
                StartDate,       
                Duration.Days( EndDate - StartDate ) + 1,
                #duration( Duree, 0, 0, 0 )
                ),

            ToTable=if bToTable
                    then
                        let
                            StrEval= "type table[ " & DateColumnName & " = Date.Type ]",
                            Eval=Expression.Evaluate(StrEval,[Date.Type=Date.Type]), 
                            t_ToTable=Table.FromList(ListDates, Splitter.SplitByNothing(), {DateColumnName}, Eval, ExtraValues.Error)
                        in 
                            t_ToTable
                    else ListDates
            
        in
            ToTable
in 
    fnListDatesBetweenV12

Exemple d'utilisation : = fnListDatesBetweenV12(StartYYMMDD,EndYYMMDD,10,true,"DATES")

fnListDatesBetweenV13
PowerQuery: 
let  fnListDatesBetweenV13 =
    (
          pStartDate as any,
          pEndDate as any,
          optional pDateColumnName as text
    ) =>

      let
        //***********************************************************************************************
        // Gestion des paramètres
        // On converti la chaine date en type date
        //***********************************************************************************************
        StartDate = Date.From(pStartDate),
        EndDate = Date.From(pEndDate),
        DateColumnName=if pDateColumnName is null then "DATE" else pDateColumnName,


            //---------------------------------------------------------------------------------------------------------
            // Construction de la liste
            //---------------------------------------------------------------------------------------------------------
            ListDates= { Number.From( StartDate ) .. Number.From( EndDate ) },

            ToTable=                   
                    let
                        StrEval= "type table[ " & DateColumnName & " = Date.Type ]",
                        Eval=Expression.Evaluate(StrEval,[Date.Type=Date.Type]), 
                        t_ToTable=Table.FromList(ListDates, Splitter.SplitByNothing(), {DateColumnName}, Eval, ExtraValues.Error),
                        t_RenTable = Table.TransformColumnTypes(t_ToTable,{{DateColumnName, type date}})           
                    in 
                        t_RenTable
                    
            
        in
            ToTable
in 
    fnListDatesBetweenV13

Là ici pas d'autres choix de forcer le type via cette fonction Table.TransformColumnTypes

Exemple d'utilisation : = fnListDatesBetweenV13(StartYYMMDD,EndYYMMDD,"DATES")

fnListDatesBetweenV14
PowerQuery: 
let fnListDatesBetweenV14 =
    (
          pStartDate as any,
          pEndDate as any,
          optional pToTable as logical,
          optional pDateColumnName as text
    ) =>
 
    let
        StartDate = Date.From(pStartDate),
        EndDate = Date.From(pEndDate),
        bToTable=if pToTable is null  then false else pToTable,
        DateColumnName=if pDateColumnName is null then "DATE" else pDateColumnName,

      //---------------------------------------------------------------------------------------------------------
      // Création d'une fonction spécifique dans la requête puis appel de celle-ci
      // La fonction fourni une table de dates
      //---------------------------------------------------------------------------------------------------------
        generateDates = (startDate as date, endDate as date) as list =>
          let
            tmp_List = List.Generate(() => startDate, each _ <= endDate, each Date.AddDays(_, 1))
          in
            tmp_List,

        //---------------------------------------------------------------------------------------------------------
        // Appel de la fonction
        //---------------------------------------------------------------------------------------------------------
        t_ToTableDate = generateDates(StartYYMMDD, EndYYMMDD),
        
        ToTableDate=if bToTable
                then
                    let
                        StrEval= "type table[ " & DateColumnName & " = Date.Type ]",
                        Eval=Expression.Evaluate(StrEval,[Date.Type=Date.Type]), 
                        t_ToTable=Table.FromList(t_ToTableDate, Splitter.SplitByNothing(), {DateColumnName}, Eval, ExtraValues.Error)
                    in 
                        t_ToTable
                else t_ToTableDate
    in
        ToTableDate
in
  fnListDatesBetweenV14

Subtilité : on passe par l'appel de cette fonction interne au code
t_ToTableDate = generateDates(StartYYMMDD, EndYYMMDD),

Exemple d'utilisation : = fnListDatesBetweenV14(StartYYMMDD,EndYYMMDD,true ,"DATES" )

fnListDatesBetweenV20
PowerQuery: 
let fnListDatesBetweenV20 =
    (
          pStartDate as any,
          pEndDate as any,
          optional pToTable as logical,
          optional pDateColumnName as text
    ) =>

      let
        //***********************************************************************************************
        // Gestion des paramètres
        // On converti la chaine date en type date
        //***********************************************************************************************
        StartDate = Date.From(pStartDate),
        EndDate = Date.From(pEndDate),
        bToTable=if pToTable is null  then false else pToTable,
        DateColumnName=if pDateColumnName is null then "DATE" else pDateColumnName,

        ListLoopDate = List.Generate(
            () => StartYYMMDD,           
            each _ <= EndYYMMDD,         
            each Date.AddDays(_, 1 )),

        TableLoopListDates= if bToTable
                        then 
                            let 
                                t_ToTable = Table.FromList(ListLoopDate, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                                t_RenameColumnDate = Table.RenameColumns(t_ToTable,{{"Column1", DateColumnName}})
                            in 
                                t_RenameColumnDate
                        else
                                ListLoopDate
      in
          TableLoopListDates
in 
    fnListDatesBetweenV20

fnListDatesBetweenV30
PowerQuery: 
let fnListDatesBetweenV30 =
    (
          pStartDate as any,
          pEndDate as any,
          optional pToTable as logical,
          optional pDateColumnName as text
    ) =>

      let
        //***********************************************************************************************
        // Gestion des paramètres
        // On converti la chaine date en type date
        //***********************************************************************************************
        StartDate = Date.From(pStartDate),
        EndDate = Date.From(pEndDate),
        bToTable=if pToTable is null  then false else pToTable,
        DateColumnName=if pDateColumnName is null then "DATE" else pDateColumnName,

        //************************************************************************************************
        // Petite précaution on aligne les dates correctement pour éviter les erreurs
        //*************************************************************************************************
        Ad_StartOK = List.Min({StartDate, EndDate}),
        Ad_EndOK = List.Max({StartDate, EndDate}),

        //*****************************************************************************************************************
        // FONCTION GetListDates
        // Génération d'une série de date
        // Le principe est simple : à partir de la date de début on ajoute 1 tant que on ne dépasse pas la date de fin
        //*****************************************************************************************************************
        GetListDates =
          (pStart as date, pEnd as date, ListDates as list)=>
            if pStart > pEnd
            then ListDates // Nous avons atteint la limite
            // Sinon à ajoute 1 jour à la liste
            // @GetListDates : c'est fonction récurrente qui s'appelle elle-même symbolisé par le signe @
            else @GetListDates(Date.AddDays(pStart, 1), pEnd, List.Combine({ListDates, {pStart}})),

        //*****************************************************************************************************************
        // Section principale et appel de la fonction permettant de générer la liste de dates
        //*****************************************************************************************************************
        t_Dates = GetListDates(Ad_StartOK, Ad_EndOK, {}),
        TableListDates= if bToTable
                        then 
                            let 
                                t_ToTable = Table.FromList(t_Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                                t_RenameColumnDate = Table.RenameColumns(t_ToTable,{{"Column1", DateColumnName}})
                            in 
                                t_RenameColumnDate
                        else
                                t_Dates

      in TableListDates
in 
  fnListDatesBetweenV30

Subtilité : on la génère via une boucle avec appel récursif

PowerQuery: 
        GetListDates =
          (pStart as date, pEnd as date, ListDates as list)=>
            if pStart > pEnd
            then ListDates // Nous avons atteint la limite
            // Sinon à ajoute 1 jour à la liste
            // @GetListDates : c'est fonction récurrente qui s'appelle elle-même symbolisé par le signe @
            else @GetListDates(Date.AddDays(pStart, 1), pEnd, List.Combine({ListDates, {pStart}})),

Fonction calendrier simplifiée
PowerQuery: 
(start as any, end as any) =>
    let
        StartDate = Date.From(start),
        EndDate = Date.From(end),
        Source = fnListDatesBetweenV30(StartDate, EndDate),
        FromList = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Date = Table.RenameColumns(FromList,{{"Column1", "DATE"}}),
        Month = Table.AddColumn(Date, "MOIS", each Date.MonthName([DATE])),
        MonthNum = Table.AddColumn(Month, "NUM_MOIS", each Date.Month([DATE])),
        WeekStartDate = Table.AddColumn(MonthNum, "DATE_DEBUT_SEMAINE", each Date.StartOfWeek([DATE])),
        WeekStart = Table.AddColumn(WeekStartDate, "JOUR_DEBUT_SEMAINE", each [MOIS] & " " & Text.From(Date.Day([DATE_DEBUT_SEMAINE]))),
        Year = Table.AddColumn(WeekStart, "ANNEE", each Date.Year([DATE])),
        YearMonth = Table.AddColumn(Year, "ANNEE_MOIS", each Number.From(Text.From([ANNEE]) & (if [NUM_MOIS] < 10 then "0" else "") & Text.From([NUM_MOIS]))),
        Result = YearMonth
    in
        Result

Fonction calendrier étendue : fnCalendarExtended
PowerQuery: 
let fnCalendarExtended =     (
          pStartDate as any,
          pEndDate as any,
          optional pToTable as logical,
          optional pDateColumnName as text
    ) =>
        let
            Today = Date.From( DateTime.LocalNow()),

            //---------------------------------------------------------------------------------------------------------
            // Bornes du calendrier
            //---------------------------------------------------------------------------------------------------------
            StartDate = Date.From(pStartDate),
            EndDate = Date.From(pEndDate),
            bToTable=if pToTable is null  then false else pToTable,
            DateColumnName=if pDateColumnName is null then "DATE" else pDateColumnName,

            //---------------------------------------------------------------------------------------------------------
            // Eléments du calendrier
            //---------------------------------------------------------------------------------------------------------

            //---------------------------------------------------------------------------------------------------------
            // Construction de la liste des dates
            //---------------------------------------------------------------------------------------------------------
            ListDates = List.Dates( StartDate, Duration.Days( EndDate - StartDate ) +1, #duration( 1, 0, 0, 0 ) ),

            //---------------------------------------------------------------------------------------------------------
            // Construction de la table des dates
            //---------------------------------------------------------------------------------------------------------   
            ToTableDate = Table.FromList( ListDates, Splitter.SplitByNothing(), type table[Date = Date.Type] ),

            //---------------------------------------------------------------------------------------------------------
            // Construction des propriétés du calendrier
            //---------------------------------------------------------------------------------------------------------
            DateAAAAMMJJ = Table.AddColumn(ToTableDate, "DateAAAAMMJJ", each Number.From( Date.ToText( [Date], "yyyyMMdd" ) ), Int64.Type ),
            Annee_1 = Table.AddColumn(DateAAAAMMJJ, "Annee", each Date.Year([Date]), Int64.Type),
            Annee_Mois = Table.AddColumn(Annee_1, "YYYY-MM", each Date.ToText( [Date], "yyyy-MM"), type text),
            Mois_Annee = Table.AddColumn(Annee_Mois, "Mois_Annee", each Date.ToText( [Date], "MMM yyyy"), type text),
            Num_Mois = Table.AddColumn(Mois_Annee, "Num Mois", each Date.Month([Date]), Int64.Type),
            Nom_Mois = Table.AddColumn(Num_Mois, "Mois", each Date.MonthName([Date], "FR-fr"), type text),
            Nom_Mois_Court = Table.AddColumn(Nom_Mois, "Mois Court", each Date.ToText( [Date] , "MMM", "FR-fr" ), type text),
            Deb_Mois = Table.AddColumn(Nom_Mois_Court, "Debut Du Mois", each Date.StartOfMonth([Date]), type date),
            Fin_Mois = Table.AddColumn(Deb_Mois, "Fin Du Mois", each Date.EndOfMonth( [Date] ), type date),
            Nbr_Jours_Mois = Table.AddColumn(Fin_Mois, "Nbr Jours Mois", each Date.DaysInMonth([Date]), Int64.Type),
            Num_Semaine = Table.AddColumn(Nbr_Jours_Mois, "Num Semaine ISO", each NumberOfWeekISO([Date])),
            Debut_Semaine = Table.AddColumn(Num_Semaine, "Debut Semaine", each Date.StartOfWeek([Date], Day.Monday ), type date),
            Jour_Long = Table.AddColumn(Debut_Semaine, "Jour", each Date.DayOfWeekName([Date], "FR-fr" ), type text),
            Jour_Court = Table.AddColumn( Jour_Long, "Jour Court", each Date.ToText( [Date], "ddd", "FR-fr" ), type text),   
            Numero_Jour_Semaine = Table.AddColumn(Jour_Court, "Num Jour Semaine", each Date.DayOfWeek( [Date], Day.Monday ), Int64.Type),
            Numero_Jour_Annee = Table.AddColumn(Numero_Jour_Semaine, "Jour Julien", each Date.DayOfYear( [Date] ), Int64.Type),
            WeekEnd = Table.AddColumn(Numero_Jour_Annee, "WeekEnd", each if Date.DayOfWeek( [Date] ) >= 5 then "Oui" else "Non", type text ),
            JourOuvre = Table.AddColumn(WeekEnd, "Jour Ouvre", each if Date.DayOfWeek( [Date] ) < 5 then "Oui" else "Non", type text ),
            Fin=JourOuvre
        in
            Fin
in 
    fnCalendarExtended

Connaître la date actuelle
PowerQuery: 
() =>
let   
    Today = Date.From( DateTime.LocalNow())   
in 
    Today

Avec l'heure
PowerQuery: 
() =>
let   
    TodayDateTime = DateTime.From( DateTime.LocalNow())   
in 
    TodayDateTime

L'heure :
PowerQuery: 
() =>
let   
    TodayDateTime = Time.From( DateTime.LocalNow())
    
    
in 
    TodayDateTime

Et un petit rappel pour mémoire du calcul du N° de semaine
PowerQuery: 
(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

Je vous laisse découvrir en détail le fichier joint
 

Pièces jointes

  • ODAT_OutilDate_V0.055.xlsx
    48.8 KB · Affichages: 1
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

POWERQUERY :: Combiner les onglets d'un classeur Excel ou certains au cas par cas
Réponses
0
Affichages
250
oguruma
POWERQUERY :: Le VLOOKUP d'Excel
Réponses
0
Affichages
284
oguruma
POWERQUERY :: Les boucles avec List.Generate
Réponses
1
Affichages
1 K
oguruma
POWERQUERY :: Combiner un dossier Excel avec table de renommage des colonnes
Réponses
0
Affichages
235
oguruma
POWERQUERY :: Extraction entre délimiteurs/Nettoyage de caractères suite....
Réponses
0
Affichages
162
oguruma
POWERQUERY :: Importation d'un fichier texte .csv et sélection dynamique entre deux dates
Réponses
1
Affichages
279
oguruma
POWERQUERY :: Importer un fichier .csv avec construction de critères dynamiques (5 max) de type texte et sélection entre deux dates possible - V2.0
Réponses
0
Affichages
317
oguruma
POWERQUERY :: Attribuer un type de données à tout ou partie des colonnes d'une table
Réponses
0
Affichages
228
oguruma
POWERQUERY :: RANKING - Une méthode
Réponses
0
Affichages
455
oguruma
POWERQUERY :: Ranking par catégorie :: TopN variable d'une liste
Réponses
0
Affichages
201
oguruma
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…