POWERQUERY :: LES DATES REMARQUABLES

oguruma · Mardi à 16:32

Bonjour le Forum,
tout comme sur Excel il est possible de créer des fonctions qui renvoient les dates remarquables comme le 1er janvier, date de Pâques, etc. etc.

1er janvier

PowerQuery:

(optional pDate as any, optional pFormat as text) =>
let
    Dat=if pDate is date
    then  #date(Date.Year(pDate ?? fnAujourdhui()),1,1)
    else #date( pDate ?? Date.Year(fnAujourdhui()),1,1)
in
    if Text.Upper(pFormat)="T" then fnDate2Text(Dat) else Dat

Par défaut on affichera Pâques de l'année en cours. On peut soit passer l'année (2025) ou passer une date

Optionnellement on peut afficher la date au format texte via la fonction fnDate2Text

PowerQuery:

let fnToText = (pDate as date, optional pFormat as text) as any =>

  let
    DateText=Date.ToText(pDate, [Format=if pFormat is null then "ddd dd MMM yyyy" else pFormat, Culture="fr-FR"])
  in
    DateText
in
  fnToText

Calcul de la date de Pâques
Le calcul de cette date est largement documenté sur la toile. Ici dans cette fonction on trouve la traduction simplifiée et adaptée à PowerQuery.

PowerQuery:

let fnPaques = (optional pAnnee as any, optional pFmt as text) as any =>

  let
   
    INT_Annee=if pAnnee is number then pAnnee else Date.Year(pAnnee ?? fnAujourdhui()),

    INT_MODULO_19=Number.Mod(INT_Annee,19),
    INT_MODULO_30=Number.Mod(234 - (11 * INT_MODULO_19),30),
    INT_DIV_7=Number.From(#date(INT_Annee, 4,INT_MODULO_30))/7,
    INT_MULTIPLE_7=Number.Round(INT_DIV_7,0) * 7 - 6,
    DATE_PASCALE=Date.From(INT_MULTIPLE_7)
  in  
    if Text.Upper(pFmt) = "T" then fnDate2Text(DATE_PASCALE) else DATE_PASCALE
in
  fnPaques

On prévoit de l'afficher si besoin au format texte.

Lundi de Pâques

PowerQuery:

(pAnnee as any) as date =>

  let  

    LundiDePaques=Date.AddDays(fnDateDePaques(pAnnee),1)

  in
    LundiDePaques

C'est assez simple il suffit d'ajouter 1 à la date de Pâques.
L'affichage au format texte n'est pas prévu mais vous pouvez vous inspirer du code du 1er janvier si nécessaire.

Pentecôte

PowerQuery:

(pAnnee as any) as date =>

  let  

    LundiDePaques=Date.AddDays(fnDateDePaques(pAnnee),49)

  in
    LundiDePaques

49 jours après Pâques

Lundi de Pentecôte

PowerQuery:

(pAnnee as any) as date =>

  let  

    LundiDePaques=Date.AddDays(fnDateDePaques(pAnnee),50)

  in
    LundiDePaques

Là pour le coup on ajoute 50 jours mais aussi c'est +1 sur le dimanche

1er Mai

PowerQuery:

(optional pDate as any) =>
    if pDate is date then #date(Date.Year(pDate ?? fnAujourdhui()),5,1)
    else #date( pDate ?? Date.Year(fnAujourdhui()),5,1)

8 Mai

PowerQuery:

(optional pDate as any) =>
    if pDate is date then #date(Date.Year(pDate ?? fnAujourdhui()),5,8)
    else #date( pDate ?? Date.Year(fnAujourdhui()),5,8)

Fête national 14 Juillet

PowerQuery:

(optional pDate as any ) =>
    if pDate is date then #date(Date.Year(pDate ?? fnAujourdhui()),7,14)
    else #date( pDate ?? Date.Year(fnAujourdhui()),7,14)

Assomption 15 Aoüt

PowerQuery:

(optional pDate as any ) =>
    if pDate is date then #date(Date.Year(pDate ?? fnAujourdhui()),8,15)
    else #date( pDate ?? Date.Year(fnAujourdhui()),8,15)

Toussaints 1er Novembre

PowerQuery:

(optional pDate as any) =>
    if pDate is date then #date(Date.Year(pDate ?? fnAujourdhui()),11,1)
    else #date( pDate ?? Date.Year(fnAujourdhui()),11,1)

Armistice 11 Novembre

PowerQuery:

(optional pDate as any) =>
    if pDate is date then #date(Date.Year(pDate ?? fnAujourdhui()),11,11)
    else #date( pDate ?? Date.Year(fnAujourdhui()),11,1)

Noël 25 Décembre

PowerQuery:

(optional pDate as any) =>
    if pDate is date then #date(Date.Year(pDate ?? fnAujourdhui()),11,1)
    else #date( pDate ?? Date.Year(fnAujourdhui()),12,25)

On peut aussi cataloguer ce que j'appelle les dates usuelles comme

Aujourd'hui

PowerQuery:

() =>
    Date.From(DateTime.FixedLocalNow())

Version 2
DateTime.Date(DateTime.LocalNow())

Demain

PowerQuery:

(optional pDate as date) =>
    Date.AddDays(pDate ?? Date.From(DateTime.FixedLocalNow()), 1)

Hier

PowerQuery:

(optional pDate as date) =>
    Date.AddDays(pDate ?? Date.From(DateTime.FixedLocalNow()), -1)

Début du mois précédent

PowerQuery:

(optional pDate as date) =>
    Date.StartOfMonth(Date.AddMonths(pDate ?? Date.From(DateTime.FixedLocalNow()), -1))

Fin du mois précédent

PowerQuery:

(optional pDate as date) =>
    Date.EndOfMonth(Date.AddMonths(pDate ?? Date.From(DateTime.FixedLocalNow()), -1))

Début du mois suivant

PowerQuery:

(optional pDate as date) =>  
    Date.StartOfMonth(Date.AddMonths(pDate ?? Date.From(DateTime.FixedLocalNow()), +1))

Fin du mois suivant

PowerQuery:

(optional pDate as date) =>
    Date.EndOfMonth(Date.AddMonths(pDate ?? Date.From(DateTime.FixedLocalNow()), +1))

Prochain jour ouvré
ATTENTION : cette version ne tient pas compte des lundis.. féries etc. il faudrait mettre en place une table annexe pour gérer les cas spécifiques

PowerQuery:

(pDate as date) as date =>
 Date.AddDays(
  pDate,            
  1                  
   + List.Count(      
      List.Generate(  
         () => Date.AddDays( pDate, 1 ),
         each Date.DayOfWeek(_, Day.Monday) >= 5,
         each Date.AddDays(_, 1)
      )
    )
)

Au passage... un petit rappel sur la notion de boucle en PowerQuery avec List.Generate

1er lundi du moi
Il peut être nécessaire parfois de connaître le 1er lundi du mois comme référence dans un planning

PowerQuery:

let fnFirstMondayOfMonth = ( Date as date ) as date =>
    let
        DateStartOfMonth         = Date.StartOfMonth(Date),
        DateStartOfMonth_Weekday = Date.DayOfWeek(DateStartOfMonth, Day.Monday),
        DaysToAdd   = Number.Mod(7 - DateStartOfMonth_Weekday, 7)
    in
        Date.AddDays(DateStartOfMonth, DaysToAdd)
in
    fnFirstMondayOfMonth

Challenge : calculer le dernier lundi du mois 🙂

Cibler n'importe quel jour de la semaine soit le prochain lundi... mardi... mercredi.... samedi... Dimanche

pAnyDay = 0 pour dimanche..... 6 pour samedi

Version 1

PowerQuery:

let fnFirstMondayOfMonth = ( Date as date, pAnyDay as number ) as date =>
    let
        DateStartOfMonth         = Date.StartOfMonth(Date),
        DateStartOfMonth_Weekday = Date.DayOfWeek(DateStartOfMonth, pAnyDay),
        // Ici on passe par un modulo 7
        DaysToAdd   = Number.Mod(7 - DateStartOfMonth_Weekday, 7)
    in
        Date.AddDays(DateStartOfMonth, DaysToAdd)
in
    fnFirstMondayOfMonth

Version 2

PowerQuery:

let fnFirstAnyOfMonthV2= ( Date as date, pAnyDay as number ) as date =>
    let

        DateStartOfMonth = Date.StartOfMonth(Date),
        DateAddDays=Date.AddDays(DateStartOfMonth,6),
        DateStartOfWeek  = Date.StartOfWeek(DateAddDays, pAnyDay)

    in
        DateStartOfWeek
in
    fnFirstAnyOfMonthV2

Nombre de jours dans un mois

PowerQuery:

(optional pDate as date) =>
    Date.Day(Date.EndOfMonth(pDate ?? fnAujourdhui()))
   
// ******************  
// autre version
// ******************
let fnNbrDaysFromToday = (pDate as any) as number =>

    let
        vDate=if pDate is date then pDate else Date.FromText(pDate),
        INT_DurationDays = Duration.Days(DateTime.Date(DateTime.LocalNow()) - vDate )
    in
        INT_DurationDays
in
    fnNbrDaysFromToday

Mois précédent - Mois suivant

PowerQuery:

// mois précédent
(optional pDate as date) =>
    Date.Day(Date.EndOfMonth(Date.AddMonths(pDate ?? fnAujourdhui(),-1)))
   
// mois suivant
(optional pDate as date) =>
    Date.Day(Date.EndOfMonth(Date.AddMonths(pDate ?? fnAujourdhui(),+1)))

Les dates Juliennes

Version 1
Cette version ne permet pas de distinguer le passage à l'an 2000

PowerQuery:

let fnJulianDate=(pDate as date) as any =>
        let
            ANNEE=Date.Year(pDate),
            DATE_JJMMAAAA=pDate,
            DATE_JULIENNE = Text.From(ANNEE-1900) & Text.PadStart(Text.From(Date.DayOfYear(DATE_JJMMAAAA)),3,"0")
        in
            DATE_JULIENNE
in
    fnJulianDate

Version 2
Cette version tient compte du passage à l'an 2000 comme dans certains ERP où l'astuce est la suivante
préfixe 0 : années 1900 => 098245 pour le 245 ème jour ce l'année 1998
préfixe 1 : années 2000 => 125030 pour le 30 ème jour de l'année 2025

PowerQuery:

let fnJULIAN_DATE_V2=(pDate as date) as any =>
let
      DATE = pDate,
      //********************************************************
      // Constantes
      //********************************************************
      INT_SIECLE = 1000,
      INT_BASE_1900=1900,
      STR_FORMAT="000000",
      //********************************************************
      // On va extraire l'année par rapport au siècle
      //********************************************************
      ANNEE_DATE = Date.Year (DATE) - INT_BASE_1900,
      //********************************************************
      // Numéro du jour jjj dans l'année
      //********************************************************
      INT_NB_JOURS_ANNEE = Date.DayOfYear (DATE),  
      //********************************************************
      // Construction de la date JULIENNE
      //********************************************************
      DATE_JULIENNE = (ANNEE_DATE * INT_SIECLE) + INT_NB_JOURS_ANNEE,
      //********************************************************
      // Formatage pour retenir le zéro non significatif
      // pour les années inférieures à 2000
      // Ainsi on a:
      // 0AAJJJ pour les années < 2000
      // 1AAJJJ pour les années >= 2000
      // La date JULIENNE est renvoyée au format texte
      // Number.ToText correspond à la fonction TEXTE() Excel
      //********************************************************
      DATE_SIECLE_JULIENNE = Number.ToText (DATE_JULIENNE, STR_FORMAT)
    in
      DATE_SIECLE_JULIENNE
   
in
  fnJULIAN_DATE_V2

Version 3

Format AAAANNN

PowerQuery:

let fnJULIAN_DATE_V3=(pDate as date) as any =>
    let
      DATE = pDate,
      //********************************************************
      // Constantes
      //********************************************************
      INT_SIECLE = 1000,
      INT_BASE_1900=1900,
      STR_FORMAT="000000",
      //********************************************************
      // On va extraire l'année par rapport au siècle
      //********************************************************
      STR_ANNEE_DATE = Number.ToText(Date.Year (DATE)),
      //********************************************************
      // Numéro du jour jjj dans l'année
      //********************************************************
      STR_NB_JOURS_ANNEE = Text.PadStart(Number.ToText(Date.DayOfYear (DATE)),3,"0"),  
      //********************************************************
      // Construction de la date JULIENNE
      //********************************************************
      DATE_JULIENNE = STR_ANNEE_DATE & STR_NB_JOURS_ANNEE

    in
      DATE_JULIENNE
in
  fnJULIAN_DATE_V3

Calcul inversé à partir d'une date Julienne pour obtenir JJ/MM/AAAA

Version 1

PowerQuery:

let fnAAAAJJJ_2_JJMMAAAA_V1=(pDate as any) as date =>
let
        YYYYDDD = if pDate is text then Number.FromText(pDate) else pDate,

        INT_BASE_1000=1000,
        INT_BASE_1900=1900,
        ANY_YEAR = Number.IntegerDivide ( YYYYDDD, INT_BASE_1000 ),
        INT_NUM_JOUR_ANNEE = YYYYDDD - ( ANY_YEAR * INT_BASE_1000 ),
        DATE_BASE_1900= #date ( INT_BASE_1900,1,1),
        DATE_JJMMAAAA =
          Date.AddDays (
            Date.AddYears ( DATE_BASE_1900, ANY_YEAR - INT_BASE_1900 ),
            INT_NUM_JOUR_ANNEE - 1
          )
      in
        DATE_JJMMAAAA
in
  fnAAAAJJJ_2_JJMMAAAA_V1

Version 2

PowerQuery:

let fnAAAAJJJ_2_JJMMAAAA_V2=(pDate as any) as date =>
        let
            STR_JULIAN = if pDate is number then Text.From(pDate) else pDate,

            //*****************************************************************************************
            // On va extraire l'année et la transformer en valeur
            //*****************************************************************************************
            STR_AAAA=Text.Start(STR_JULIAN,4),
            INT_AAAA=Number.FromText(STR_AAAA),

            //*****************************************************************************************
            // On va extraire la date sans le siècle
            //*****************************************************************************************
            STR_AAJJJ=Text.End(STR_JULIAN,5),

            //*****************************************************************************************
            // On va vérifier si on est dans les années 2000 ou 1900 pour faire la conversion de siècle
            //*****************************************************************************************
            INT_BASED_SIECLE=if INT_AAAA >= 2000 then 2000 else 1900,

            //*****************************************************************************************
            // Calcul de l'année selon le siècle identifié, on ajouter le siècle calculé plus haut
            //*****************************************************************************************
            INT_ANNEE = Number.From(Text.Start(STR_AAJJJ, 2)) + INT_BASED_SIECLE,

            //*****************************************************************************************
            // Extraction du jour et formatage final de la date à partir du 1er jour de l'année
            //*****************************************************************************************
            INT_JOUR_ANNEE = Number.From(Text.End(STR_AAJJJ, 3)),
            DATE_JJMMAAAA = Date.AddDays(#date(INT_ANNEE, 1, 1), INT_JOUR_ANNEE - 1)
        in
            DATE_JJMMAAAA
in
    fnAAAAJJJ_2_JJMMAAAA_V2

Version 3

PowerQuery:

let fnAAJJJ_2_JJMMAAAA=(pDate as any,optional pSiecle as number) as date =>
        let
            STR_JULIAN = if pDate is number then Text.From(pDate) else pDate,
            INT_BASE_SIECLE=pSiecle ?? 2000,
            INT_AA = Number.From(Text.Start(STR_JULIAN, 2)) + INT_BASE_SIECLE,
            INT_DAY_YEAR = Number.From(Text.End(STR_JULIAN, 3)),
            DATE_JJMMAAAA = Date.AddDays(#date(INT_AA, 1, 1), INT_DAY_YEAR - 1)
        in
            DATE_JJMMAAAA
in
    fnAAJJJ_2_JJMMAAAA

Version 4

PowerQuery:

let fnAAJJJ_2_JJMMAAAA=(pDate as any) as any =>
        let
            STR_JULIAN = if pDate is number then Text.From(pDate) else pDate,
            STR_JULIAN_PAD = Text.PadStart(STR_JULIAN,6,"0"),
            STR_JULIAN_5=Text.End(STR_JULIAN,5),
            INT_BASE_SIECLE=if Text.Start(STR_JULIAN_PAD,1)="0" then 1900 else 2000,
            INT_AA = Number.From(Text.Start(STR_JULIAN_5, 2)) + INT_BASE_SIECLE,
            INT_DAY_YEAR = Number.From(Text.End(STR_JULIAN_5, 3)),
            DATE_JJMMAAAA = Date.AddDays(#date(INT_AA, 1, 1), INT_DAY_YEAR - 1)
        in
            // INT_BASE_SIECLE // STR_JULIAN_PAD //
            DATE_JJMMAAAA
in
    fnAAJJJ_2_JJMMAAAA

Calculs de différences entre deux dates ou entre deux temps

Différence en nombre de jours entre deux dates

PowerQuery:

let fnDiffDate_D = (pDate1 as date, optional pDate2 as date) =>
    let
        Date1=pDate1,
        Date2=pDate2 ?? fnAujourdhui(),
        Diff_D = Duration.Days(Date2 - Date1)
    in
        Diff_D
in
    fnDiffDate_D

Si on souhaite avoir le résultat au format texte

PowerQuery:

let  NumberHoursBetweenTwoTimes = (pDT1 as datetime, pDT2 as datetime, optional pFormat as text) =>
        let
            Format = Text.Upper(pFormat),
            Result  =(Duration.Days(pDT1 - pDT2) *24 ) + (Duration.Hours(pDT1 - pDT2)),
            Signe=Number.Sign(Result),
            TextResult=if Format = "T" then Text.Format("#{0} Heures",{ Number.ToText(Result * Signe,"D2") }) else Result * Signe
        in
            TextResult
in
     NumberHoursBetweenTwoTimes

Nombre d'heures entre deux temps

PowerQuery:

let  NumberHoursBetweenTwoTimes = (pDT1 as time, pDT2 as time) =>
        let
            Result  =Duration.Hours(pDT1 - pDT2),
            Signe=Number.Sign(Result)
        in
            Result * Signe
in
     NumberHoursBetweenTwoTimes

Nombre de minutes

Version 1

Code:

let  NumberHoursBetweenTwoTimes = (pDT1 as datetime, pDT2 as datetime) =>
        let
            Result  = (Duration.Hours(pDT1 - pDT2) * 60 ) + Duration.Minutes(pDT1 - pDT2),
            Signe=Number.Sign(Result)
        in
            Result * Signe
in
     NumberHoursBetweenTwoTimes

Version 2

PowerQuery:

let  NumberHoursBetweenTwoTimes = (pDT1 as datetime, pDT2 as datetime) =>
        let
            Result  =  (Duration.Days(pDT1 - pDT2) * 1440 ) + (Duration.Hours(pDT1 - pDT2) * 60 ) + Duration.Minutes(pDT1 - pDT2),
            Signe=Number.Sign(Result)
        in
            Result * Signe
in
     NumberHoursBetweenTwoTimes

Nombre de secondes

PowerQuery:

let  NumberHoursBetweenTwoTimes = (pDT1 as datetime, pDT2 as datetime) =>
        let
            Result  =  ((Duration.Days(pDT1 - pDT2) * 1440 ) + (Duration.Hours(pDT1 - pDT2) * 60 ) + Duration.Minutes(pDT1 - pDT2)) * 60 + Duration.Seconds(pDT1 - pDT2),
            Signe=Number.Sign(Result)
        in
            Result * Signe
in
     NumberHoursBetweenTwoTimes

Au format texte

PowerQuery:

let  NumberHoursBetweenTwoTimes = (pDT1 as datetime, pDT2 as datetime, optional pFormat as text) =>
        let
            Format=Text.Upper(pFormat),
            Result  =  (Duration.Days(pDT1 - pDT2) * 1440 ) + (Duration.Hours(pDT1 - pDT2) * 60 ) + Duration.Minutes(pDT1 - pDT2),
            Signe=Number.Sign(Result),
            TextResult=if Format="T"
                       then Text.Format("#{0} Jour(s) - #{1} Heure(s) - #{2} Minute(s)",
                                        {(Duration.Days(pDT1 - pDT2) * Signe), (Duration.Hours(pDT1 - pDT2) * Signe ), Duration.Minutes(pDT1 - pDT2) * Signe })    
                       else Result * Signe
        in
            TextResult
in
     NumberHoursBetweenTwoTimes

Quelques fonctions automatiques pour les différences sur les dates
Différence entre deux jours

PowerQuery:

let fnDiffDate_D = (pDate1 as date, optional pDate2 as date) =>
    let
        Date1=pDate1,
        Date2=pDate2 ?? fnAujourdhui(),
        Diff_D = Duration.Days(Date2 - Date1)
    in
        Diff_D
in
    fnDiffDate_D

Différence en nbr de mois

PowerQuery:

let fnDiffDate_M=(pDate1 as date, optional pDate2 as date) as number =>
        let
            Date1=pDate1,
            Date2=pDate2 ?? fnAujourdhui(),
            DeltaDateYear=Date.Year(Date2)-Date.Year(Date1),
            DateDiff=(DeltaDateYear * 12) + Date.Month(Date2) - Date.Month(Date1)
        in
            DateDiff
in
    fnDiffDate_M

Une autre version

PowerQuery:

let fnDiffDate_M=(pDate1 as date, optional pDate2 as date) as number =>
    let
        Date1=pDate1,
        Date2=pDate2 ?? fnAujourdhui(),
        DurationDays=Duration.Days(Date2 - Date1), 
        // On est ici plus précis pour tenir compte des années bissextiles
        Diff365=Number.From((Date2 - Date1)/ (365.25/12)),
        NumberFromDiff365=Number.From(Diff365),
        Diff_M= Number.Round(NumberFromDiff365,0)
    in
        Diff_M
in
  fnDiffDate_M

En nombre d'années

PowerQuery:

let fnDiffDate_Y = (pDate1 as date, optional pDate2 as date) as number =>
    let
        Date1=pDate1,
        Date2=pDate2 ?? fnAujourdhui(),
        DiffDate = Duration.Days(Date2 - Date1),
        DurationTotalDays = (Duration.TotalDays(Duration.From(DiffDate)) / 365.25),
        Diff_Y = Number.RoundDown(DurationTotalDays)
    in
        DiffDate/365.25 //Diff_Y
in
    fnDiffDate_Y

Une version avec choix sur le type de différence un peu comme DiffDate sur Excel

PowerQuery:

let fnDiffDate = (pDate1 as date, optional pDate2 as date, optional pFormat as text) =>
    let
        Date1=pDate1,
        Date2=pDate2 ?? fnAujourdhui(),
        Format=Text.Upper(pFormat) ?? "D",
        
        Diff_D = () =>
            let
                Result=Duration.Days(Date2 - Date1)
            in
                Result,

        Diff_M = () =>
            let
                DeltaDateYear=Date.Year(Date2)-Date.Year(Date1),
                Result=(DeltaDateYear * 12) + Date.Month(Date2) - Date.Month(Date1)
            in
                Result,

        Diff_Y = () =>
            let
                DiffDate = Duration.Days(Date2 - Date1),
                DurationTotalDays = (Duration.TotalDays(Duration.From(DiffDate)) / 365.25),
                Result = Number.RoundDown(DurationTotalDays)     
            in
                Result,

        Diff_YT = () =>
            let
                DiffY=Duration.Days(Date2 - Date1),
                DiffDate = Duration.Days(Date2 - Date1),
                DurationTotalDays=Duration.TotalDays(Duration.From(DiffDate)),
                NBAnnees=Number.IntegerDivide(DurationTotalDays,365.25),               
                NBMois=Number.IntegerDivide(DurationTotalDays,30.25), 
                ResteMois= NBMois - (NBAnnees * 12) ,               
                Result= if ResteMois=0 then Number.ToText(NBAnnees,"D2") & " ans " else Number.ToText(NBAnnees,"D2") & " ans " & Number.ToText(ResteMois,"D2") & " mois "
                
            in
                Result,


        Diff = if Format = "D" then Diff_D()
               else if Format = "M" then Diff_M()
               else if Format = "Y" then Diff_Y()
               else if Format = "YT" then Diff_YT()
               else null
            
    in
        Diff
in
    fnDiffDate

Un petit focus sur la génération de dates

Obtenir le 1er de chaque mois

Version 1

PowerQuery:

let fnLST_DISTINCT_DATE=(pDeb as date, pFin as date) as table =>
        let
            DATE_DEB=pDeb,
            DATE_FIN=pFin,
            LST_DATES=List.Distinct(List.Transform({Number.From(DATE_DEB)..Number.From(DATE_FIN)}, each Date.StartOfMonth(Date.From(_)))),
            TBL_2_TABLE = Table.FromList(LST_DATES, Splitter.SplitByNothing(), {"DATE"}, null, ExtraValues.Error)
        in
            TBL_2_TABLE
in
    fnLST_DISTINCT_DATE

Version 2

PowerQuery:

let fnLST_GENERATE_DATE=(pDeb as date, pFin as date) as table =>
        let
            DATE_DEB=pDeb,
            DATE_FIN=pFin,
            LST_DATES=let s=Date.StartOfMonth(DATE_DEB), e=DATE_FIN in List.Generate(()=>s, each _<=e, each Date.AddMonths(_,1)),
            TBL_2_TABLE = Table.FromList(LST_DATES, Splitter.SplitByNothing(), {"DATE"}, null, ExtraValues.Error)
        in
            TBL_2_TABLE
in
    fnLST_GENERATE_DATE

Variante quand les dates sont passées au format texte

PowerQuery:

let fnLST_DISTINCT_DATE=(pDeb as text, pFin as text) as table =>
        let
            DATE_DEB=Date.FromText(pDeb,"fr-FR"),
            DATE_FIN=Date.FromText(pFin,"fr-FR"),

            LST_DATES=List.Distinct(List.Transform({Number.From(DATE_DEB)..Number.From(DATE_FIN)}, each Date.StartOfMonth(Date.From(_)))),
            TBL_2_TABLE = Table.FromList(LST_DATES, Splitter.SplitByNothing(), {"DATE"}, null, ExtraValues.Error)
        in
            TBL_2_TABLE
in
    fnLST_DISTINCT_DATE

Génération de calendriers

Version 1

PowerQuery:

let fnCalendar=(pStartDate as any, pEndDate as any, optional pCulture as nullable text) as table =>
        let
        
          ppStartDate=if pStartDate is date then pStartDate else Date.FromText(pStartDate,if pCulture is null then "fr-FR" else pCulture),
          ppEndDate=if pEndDate is date then pEndDate else Date.FromText(pEndDate,if pCulture is null then "fr-FR" else pCulture),

          INT_DayCount = Duration.Days(Duration.From(ppEndDate - ppStartDate)),
          TBL_Source = List.Dates(ppStartDate,INT_DayCount,#duration(1,0,0,0)),
          TBL_TableFromList = Table.FromList(TBL_Source, Splitter.SplitByNothing()),
          TBL_ChangedType = Table.TransformColumnTypes(TBL_TableFromList,{{"Column1", type date}}),
          TBL_RenamedColumns = Table.RenameColumns(TBL_ChangedType,{{"Column1", "Date"}}),
          TBL_InsertYear = Table.AddColumn(TBL_RenamedColumns, "Année", each Date.Year([Date])),
          TBL_InsertQuarter = Table.AddColumn(TBL_InsertYear, "Trimestre", each Date.QuarterOfYear([Date])),
          TBL_InsertMonth = Table.AddColumn(TBL_InsertQuarter, "MoisNombre", each Date.Month([Date])),
          TBL_InsertDay = Table.AddColumn(TBL_InsertMonth, "Jour", each Date.Day([Date])),
          TBL_InsertDayInt = Table.AddColumn(TBL_InsertDay, "DateNombre", each [Année] * 10000 + [MoisNombre] * 100 + [Jour]),
          TBL_InsertMonthName = Table.AddColumn(TBL_InsertDayInt, "MoisNom", each Text.Proper(Date.ToText([Date], "MMMM", if pCulture is null then "fr-FR" else pCulture)), type text),
          TBL_InsertCalendarMonth = Table.AddColumn(TBL_InsertMonthName, "MoisCalendrier", each (try(Text.Range([MoisNom],0,3)) otherwise [MoisNom]) & " " & Number.ToText([Année])),
          TBL_InsertCalendarQtr = Table.AddColumn(TBL_InsertCalendarMonth, "TrimestreCalendrier", each "T" & Number.ToText([Trimestre]) & " " & Number.ToText([Année])),
          TBL_InsertDayWeek = Table.AddColumn(TBL_InsertCalendarQtr, "JourSemaine", each Date.DayOfWeek([Date])),
          TBL_InsertDayName = Table.AddColumn(TBL_InsertDayWeek, "NomJourSemaine", each Text.Proper(Date.ToText([Date], "dddd", if pCulture is null then "fr-FR" else pCulture)), type text),
          TBL_InsertWeekEnding = Table.AddColumn(TBL_InsertDayName, "FinSemaine", each Date.EndOfWeek([Date]), type date),
          TBL_InsertWeekOfYear= Table.AddColumn(TBL_InsertWeekEnding , "NumSemaine", each "S" & Text.PadStart(Number.ToText(Date.WeekOfYear([Date])),2,"0")   )

        in
          TBL_InsertWeekOfYear
in
  fnCalendar

Version 2
let fnCalendrierV1=(pDebut as number, pFin as number) as table =>
let

LST_Colonnes={"DATE", "ANNEE", "NOM_MOIS", "NOM_JOUR", "JJJ", "NUM_SEMAINE", "ID_NUM_JOUR", "AAMM", "ID_NUM_MOIS", "ANNEE_FISCALE", "MOIS_FISCAL", "AAMM_ FISCAL"},

Debut = #date(pDebut, 1, 1),
// Today = DateTime.Date(DateTime.LocalNow()),
Fin = #date(pFin, 12, 31),
INT_Length = Duration.Days(Fin-Debut),

Source = List.Dates(Debut, INT_Length, #duration(1, 0, 0, 0)),
TBL_2_TABLE = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
TBL_REN_COLS_1 = Table.RenameColumns(TBL_2_TABLE,{{"Column1", "DATE"}}),
TBL_CHANGE_TYPE = Table.TransformColumnTypes(TBL_REN_COLS_1,{{"DATE", type date}}),
TBL_INIT = TBL_CHANGE_TYPE,
TBL_ADD_YEAR = Table.AddColumn(TBL_INIT, "ANNEE", each Date.Year([DATE]), Int64.Type),

TBL_ADD_FISC_YEAR = Table.AddColumn(TBL_ADD_YEAR , "ANNEE_FISCALE", each Date.Year([DATE]+#duration(184,0,0,0)), Int64.Type),

TBL_ADD_MONTH_NAME = Table.AddColumn(TBL_ADD_FISC_YEAR , "NOM_MOIS", each Text.Proper(Date.MonthName([DATE],"fr-FR")), type text),
TBL_ADD_DAY_NAME = Table.AddColumn(TBL_ADD_MONTH_NAME, "NOM_JOUR", each Text.Proper(Date.DayOfWeekName([DATE],"fr-FR")), type text),

TBL_ADD_FISC_MONTH = Table.AddColumn(TBL_ADD_DAY_NAME , "MOIS_FISCAL", each if Date.Month([DATE]) >=7 then Date.Month([DATE])-6 else Date.Month([DATE])+6 , Int64.Type),
TBL_ADD_DAY_NUMBER = Table.AddColumn(TBL_ADD_FISC_MONTH, "ID_NUM_JOUR", each Date.Day([DATE]), Int64.Type),

TBL_ADD_NUM_DAY_WEEK = Table.AddColumn(TBL_ADD_DAY_NUMBER, "ID_NUM_JOUR_SEM", each Date.DayOfWeek(([DATE]), Day.Monday)+1, Int64.Type),
TBL_ADD_SHORT_DAY_NAME = Table.AddColumn(TBL_ADD_NUM_DAY_WEEK, "JJJ", each Text.Start([NOM_MOIS], 3), type text),
TBL_ADD_AAMM = Table.AddColumn(TBL_ADD_SHORT_DAY_NAME , "AAMM", each ([ANNEE]-2000)*100 + [ID_NUM_MOIS]),
TBL_ADD_AAMMFisc = Table.AddColumn(TBL_ADD_AAMM , "AAMM_ FISCAL", each ([ANNEE_FISCALE]-2000)*100 + [MOIS_FISCAL]),
TBL_CHANGE_TYPE_1 = Table.TransformColumnTypes(TBL_ADD_AAMMFisc,{{"ANNEE_FISCALE", Int64.Type}}),
TBL_ADD_MONTH_NUMBER = Table.AddColumn(TBL_CHANGE_TYPE_1, "ID_NUM_MOIS", each Date.Month([DATE])),
TBL_CHANGE_TYPE_2 = Table.TransformColumnTypes(TBL_ADD_MONTH_NUMBER,{{"ID_NUM_MOIS", Int64.Type}}),

TBL_NUM_SEM= Table.AddColumn(TBL_CHANGE_TYPE_2 , "NUM_SEMAINE", each "S" & Text.PadStart(Number.ToText(Date.WeekOfYear([DATE])),2,"0") ),

TBL_CALENDAR_FINAL = Table.ReorderColumns(TBL_NUM_SEM,LST_Colonnes)

in
TBL_CALENDAR_FINAL
in
fnCalendrierV1

Formatage de dates

PowerQuery:

let fnToText = (pDate as date, optional pFormat as text) as any =>

  let
    DateText=Date.ToText(pDate, [Format=if pFormat is null then "dd/MM/yyyy" else pFormat, Culture="fr-FR"])
  in
    DateText
in
  fnToText

PowerQuery:

let fnToText = (pDate as date, optional pFormat as text) as any =>

  let
    DateText=Date.ToText(pDate, [Format=if pFormat is null then "ddd dd MMM yyyy" else pFormat, Culture="fr-FR"])
  in
    DateText
in
  fnToText

Calendriers étendus avec les fonctions ci-dessus

PowerQuery:

let fnCalendrier = (pDebut as date, optional pFin as date) as table =>
        let

        //*********************************************************************************************************************
        // Génération d'un calendrier
        //*********************************************************************************************************************
            StartDate =pDebut,
            EndDate = if pFin is null then #date(Date.Year(Date.From(DateTime.FixedLocalNow())),12,31)  else pFin,
            DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,

        //*********************************************************************************************************************
        // création de la table
        //*********************************************************************************************************************
        Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
        TBL_2_TABLE = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        TBL_REN_COLS_1 = Table.RenameColumns(TBL_2_TABLE,{{"Column1", "DATE"}}),
        TBL_CHANGE_TYPE = Table.TransformColumnTypes(TBL_REN_COLS_1,{{"DATE", type date}}),
        TBL_ADD_YEAR = Table.AddColumn(TBL_CHANGE_TYPE, "ANNEE", each Date.Year([DATE]), Int64.Type),

        //*********************************************************************************************************************
        // Année fiscale au 1er août
        //*********************************************************************************************************************
        TBL_ADD_FISC_YEAR = Table.AddColumn(TBL_ADD_YEAR , "ANNEE_FISCALE", each Date.Year([DATE]+#duration(184,0,0,0)), Int64.Type),
        TBL_ADD_MONTH_NAME = Table.AddColumn(TBL_ADD_FISC_YEAR , "NOM_MOIS", each Text.Proper(Date.MonthName([DATE],"fr-FR")), type text),
        TBL_ADD_DAY_NAME = Table.AddColumn(TBL_ADD_MONTH_NAME, "NOM_JOUR", each Text.Proper(Date.DayOfWeekName([DATE],"fr-FR")), type text),
        TBL_ADD_FISC_MONTH = Table.AddColumn(TBL_ADD_DAY_NAME , "MOIS_FISCAL", each if Date.Month([DATE]) >=7 then Date.Month([DATE])-6 else Date.Month([DATE])+6 , Int64.Type),
        TBL_ADD_DAY_NUMBER = Table.AddColumn(TBL_ADD_FISC_MONTH, "ID_NUM_JOUR", each Date.Day([DATE]), Int64.Type),

        //*********************************************************************************************************************
        // numéro du jour, pour une semaine commençant le lundi
        // l’ajout +1 à la fonction DayOfWeek permet au lundi d’être numéroté 1 (au lieu du 0 par défaut)
        //*********************************************************************************************************************
        TBL_ADD_NUM_DAY_WEEK = Table.AddColumn(TBL_ADD_DAY_NUMBER, "ID_NUM_JOUR_SEM", each Date.DayOfWeek(([DATE]), Day.Monday)+1, Int64.Type),
        TBL_ADD_SHORT_DAY_NAME = Table.AddColumn(TBL_ADD_NUM_DAY_WEEK, "JJJ", each Text.Start([NOM_MOIS], 3), type text),
        TBL_CHANGE_TYPE_1 = Table.TransformColumnTypes(TBL_ADD_SHORT_DAY_NAME,{{"ANNEE_FISCALE", Int64.Type}}),
        TBL_ADD_MONTH_NUMBER = Table.AddColumn(TBL_CHANGE_TYPE_1, "ID_NUM_MOIS", each Date.Month([DATE])),
            TBL_ADD_AAMM_FISC = Table.AddColumn(TBL_ADD_MONTH_NUMBER , "AAMM_ FISCAL", each ([ANNEE_FISCALE]-2000)*100 + [MOIS_FISCAL]),
            TBL_ADD_AAMM = Table.AddColumn(TBL_ADD_AAMM_FISC , "AAMM", each ([ANNEE]-2000)*100 + [ID_NUM_MOIS]),
        TBL_CHANGE_TYPE_2 = Table.TransformColumnTypes(TBL_ADD_AAMM,{{"ID_NUM_MOIS", Int64.Type}}),
        TBL_JULIAN_1= Table.AddColumn(TBL_CHANGE_TYPE_2, "DATE_JULIENNE_1", each fnJULIAN_DATE_V3([DATE])),
            TBL_JULIAN_2 = Table.AddColumn(TBL_JULIAN_1, "DATE_JULIENNE_2", each fnJULIAN_DATE_V2([DATE])),
        TBL_NUM_SEMAINE = Table.AddColumn(TBL_JULIAN_2, "NUM_SEMAINE", each Date.WeekOfYear([DATE]), Int64.Type),
        TBL_QUARTER = Table.AddColumn(TBL_NUM_SEMAINE, "TRIMESTRE", each Date.QuarterOfYear([DATE]), Int64.Type),

        TBL_CALENDAR_FINAL = Table.ReorderColumns(TBL_QUARTER,{"DATE", "ANNEE", "NOM_MOIS", "NOM_JOUR", "JJJ", "ID_NUM_JOUR", "AAMM", "ID_NUM_MOIS", "ANNEE_FISCALE", "MOIS_FISCAL", "AAMM_ FISCAL","DATE_JULIENNE_1","DATE_JULIENNE_2","TRIMESTRE","NUM_SEMAINE"})
        in
            TBL_CALENDAR_FINAL
in
    fnCalendrier

Version 2 avec les fonctions présentées ci-dessus

PowerQuery:

let fnCalendrier = (pDebut as date, optional pFin as date) as table =>
        let

        //*********************************************************************************************************************
        // Génération d'un calendrier
        //*********************************************************************************************************************
            StartDate =pDebut,
            EndDate = if pFin is null then #date(Date.Year(Date.From(DateTime.FixedLocalNow())),12,31)  else pFin,
            DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,

        //*********************************************************************************************************************
        // création de la table
        //*********************************************************************************************************************
        Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
        TBL_2_TABLE = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        TBL_REN_COLS_1 = Table.RenameColumns(TBL_2_TABLE,{{"Column1", "DATE"}}),
        TBL_CHANGE_TYPE = Table.TransformColumnTypes(TBL_REN_COLS_1,{{"DATE", type date}}),
        TBL_INIT = TBL_CHANGE_TYPE,
        TBL_ADD_YEAR = Table.AddColumn(TBL_INIT, "ANNEE", each Date.Year([DATE]), Int64.Type),

        //*********************************************************************************************************************
        // Année fiscale au 1er août
        //*********************************************************************************************************************
        TBL_ADD_FISC_YEAR = Table.AddColumn(TBL_ADD_YEAR , "ANNEE_FISCALE", each Date.Year([DATE]+#duration(184,0,0,0)), Int64.Type),
        TBL_ADD_MONTH_NAME = Table.AddColumn(TBL_ADD_FISC_YEAR , "NOM_MOIS", each Text.Proper(Date.MonthName([DATE],"fr-FR")), type text),
        TBL_ADD_DAY_NAME = Table.AddColumn(TBL_ADD_MONTH_NAME, "NOM_JOUR", each Text.Proper(Date.DayOfWeekName([DATE],"fr-FR")), type text),
        TBL_ADD_FISC_MONTH = Table.AddColumn(TBL_ADD_DAY_NAME , "MOIS_FISCAL", each if Date.Month([DATE]) >=7 then Date.Month([DATE])-6 else Date.Month([DATE])+6 , Int64.Type),
        TBL_ADD_DAY_NUMBER = Table.AddColumn(TBL_ADD_FISC_MONTH, "ID_NUM_JOUR", each Date.Day([DATE]), Int64.Type),

        //*********************************************************************************************************************
        // numéro du jour, pour une semaine commençant le lundi
        // l’ajout +1 à la fonction DayOfWeek permet au lundi d’être numéroté 1 (au lieu du 0 par défaut)
        //*********************************************************************************************************************
        TBL_ADD_NUM_DAY_WEEK = Table.AddColumn(TBL_ADD_DAY_NUMBER, "ID_NUM_JOUR_SEM", each Date.DayOfWeek(([DATE]), Day.Monday)+1, Int64.Type),
        TBL_ADD_SHORT_DAY_NAME = Table.AddColumn(TBL_ADD_NUM_DAY_WEEK, "JJJ", each Text.Start([NOM_MOIS], 3), type text),
        TBL_CHANGE_TYPE_1 = Table.TransformColumnTypes(TBL_ADD_SHORT_DAY_NAME,{{"ANNEE_FISCALE", Int64.Type}}),
        TBL_ADD_MONTH_NUMBER = Table.AddColumn(TBL_CHANGE_TYPE_1, "ID_NUM_MOIS", each Date.Month([DATE])),
        TBL_ADD_AAMMFISC = Table.AddColumn(TBL_ADD_MONTH_NUMBER , "AAMM_ FISCAL", each ([ANNEE_FISCALE]-2000)*100 + [MOIS_FISCAL]),
        TBL_ADD_AAMM = Table.AddColumn(TBL_ADD_AAMMFISC , "AAMM", each ([ANNEE]-2000)*100 + [ID_NUM_MOIS]),
        TBL_CHANGE_TYPE_2 = Table.TransformColumnTypes(TBL_ADD_AAMM,{{"ID_NUM_MOIS", Int64.Type}}),

        TBL_J_LAN = Table.AddColumn(TBL_CHANGE_TYPE_2, "JOUR_DE_LAN", each fnJourDeLan([DATE])),
        TBL_PAQUES = Table.AddColumn(TBL_J_LAN, "PAQUES", each fnDateDePaques([ANNEE])),
        TBL_LUNDI_PAQUES = Table.AddColumn(TBL_PAQUES, "LUNDI_PAQUES", each fnLundiDePaques([ANNEE])),
        TBL_PENTECOTE = Table.AddColumn(TBL_LUNDI_PAQUES, "PENTECOTE", each fnPentecote([ANNEE])),
        TBL_LUNDI_PENTECOTE = Table.AddColumn(TBL_PENTECOTE, "LUNDI_PENTECODE", each fnLundiDePentecote([ANNEE])),
        TBL_1ERMAI = Table.AddColumn(TBL_LUNDI_PENTECOTE, "1ERMAI", each fn1erMai([DATE])),
        TBL_8MAI = Table.AddColumn(TBL_1ERMAI, "8MAI", each fn8Mai([DATE])),
        TBL_14JUILLET = Table.AddColumn(TBL_8MAI, "14JUILLET", each fnNationalDate([DATE])),
        TBL_15AOUT = Table.AddColumn(TBL_14JUILLET, "15AOUT", each fnAssomption([DATE])),
        TBL_TOUSSAINTS = Table.AddColumn(TBL_15AOUT, "TOUSSAINTS", each fnToussaints([DATE])),
        TBL_ARMISTICE = Table.AddColumn(TBL_TOUSSAINTS, "ARMISTICE", each fnArmisticeDate([DATE])),
        TBL_NOEL = Table.AddColumn(TBL_ARMISTICE, "NOEL", each fnNoel([DATE])),
        TBL_DEBUT_MOIS_PRECED = Table.AddColumn(TBL_NOEL, "DEBUT_MOIS_PRECED", each fnDebutMoisPrecedent([DATE])),
        TBL_FIN_MOIS_PRECED = Table.AddColumn(TBL_DEBUT_MOIS_PRECED, "FIN_MOIS_PRECED", each fnFinMoisPrecedent([DATE])),
        TBL_DEBUT_MOIS_SUIVANT = Table.AddColumn(TBL_FIN_MOIS_PRECED, "DEBUT_MOIS_SUIVANT", each fnDebutMoisSuivant([DATE])),
        TBL_FIN_MOIS_SUIVANT = Table.AddColumn(TBL_DEBUT_MOIS_SUIVANT, "FIN_DE_MOIS_SUIVANT", each fnFinMoisSuivant([DATE])),
        TBL_NB_JOURS_DU_MOIS = Table.AddColumn(TBL_FIN_MOIS_SUIVANT, "NB_JOURS_MOIS", each fnNbrJoursDuMois([DATE])),
        TBL_NB_JOURS_MOIS_PRECED = Table.AddColumn(TBL_NB_JOURS_DU_MOIS, "NB_JOURS_MOIS_PRECED", each fnNbrJoursMoisPrecedent([DATE])),
        TBL_NB_JOURS_MOIS_SUIVANT = Table.AddColumn(TBL_NB_JOURS_MOIS_PRECED, "NB_JOURS_MOIS_SUIVANT", each fnNbrJoursSuivant([DATE])),
        TBL_JULIAN_1 = Table.AddColumn(TBL_NB_JOURS_MOIS_SUIVANT, "JULIAN_1", each fnJULIAN_DATE_V1([DATE])),
        TBL_JULIAN_2 = Table.AddColumn(TBL_JULIAN_1, "JULIAN_2", each fnJULIAN_DATE_V2([DATE])),
        TBL_JULIAN_3 = Table.AddColumn(TBL_JULIAN_2, "JULIAN_3", each fnJULIAN_DATE_V3([DATE])),
        TBL_PREM_LUNDI_MOIS = Table.AddColumn(TBL_JULIAN_3, "PREM_LUNDI_MOIS", each fnChoixJourSemaineV1([DATE],1)),
        TBL_PERSO_1 = Table.AddColumn(TBL_PREM_LUNDI_MOIS, "PREM_MARDI_MOIS", each fnChoixJourSemaineV1([DATE],2)),
        TBL_PERSO_2 = Table.AddColumn(TBL_PERSO_1, "PREM_MERCREDI_MOIS", each fnChoixJourSemaineV1([DATE],3)),
        TBL_PERSO_3 = Table.AddColumn(TBL_PERSO_2, "PREM_JEUDI_MOIS", each fnChoixJourSemaineV1([DATE],4)),
        TBL_PERSO_4 = Table.AddColumn(TBL_PERSO_3, "PREM_VENDREI_MOIS", each fnChoixJourSemaineV1([DATE],5)),
        TBL_PERSO_5 = Table.AddColumn(TBL_PERSO_4, "PREM_SAMEDI_MOIS", each fnChoixJourSemaineV1([DATE],6)),
        TBL_PERSO_6 = Table.AddColumn(TBL_PERSO_5, "PREM_DIMANCHE_MOIS", each fnChoixJourSemaineV1([DATE],0)),
        TBL_PERSO_7 = Table.AddColumn(TBL_PERSO_6, "DERN_LUNDI_MOIS", each fnLastAnyOfMonth([DATE],1)),
        TBL_PERSO_8 = Table.AddColumn(TBL_PERSO_7, "DERN_MARDI_MOIS", each fnLastAnyOfMonth([DATE],2)),
        TBL_PERSO_9 = Table.AddColumn(TBL_PERSO_8, "DERN_MERCREDI_MOIS", each fnLastAnyOfMonth([DATE],3)),
        TBL_PERSO_10 = Table.AddColumn(TBL_PERSO_9, "DERN_JEUDI_MOIS", each fnLastAnyOfMonth([DATE],4)),
        TBL_PERSO_11 = Table.AddColumn(TBL_PERSO_10, "DERN_VENDREDI_MOIS", each fnLastAnyOfMonth([DATE],5)),
        TBL_PERSO_12 = Table.AddColumn(TBL_PERSO_11, "DERN_SAMEDI_MOIS", each fnLastAnyOfMonth([DATE],6)),
        TBL_CAL_FINAL = Table.AddColumn(TBL_PERSO_12, "DERN_DIMANCHE_MOIS", each fnLastAnyOfMonth([DATE],0))       
        in
            TBL_CAL_FINAL
in
    fnCalendrier

Un exemple avec un calcul d'ancienneté à partir d'un fichier du personnel lambda

PowerQuery:

let

    //*****************************************************************************************************
    // On récupère les paramètres permettant d'importer le fichier
    //*****************************************************************************************************
    STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
    STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
    STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),
    
    Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
    TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
    TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(TBL_PROMOTE,{{"DEPT", Int64.Type}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "ANCIENNETE", each fnDiffDate([DATE ENTREE],null,"YT"))
in
    #"Personnalisée ajoutée"

Vous trouverez le résultats de toutes les fonctions dans

sous PowerQuery

et dans ces groupes

Bien entendu ce post reste perfectible mais il peut être une bonne base pour ceux/celles qui souhaitent faire évoluer leurs calculs sur les dates et y ajouter d'autres dates remarquables. On aurait pu mettre Mardi Gras 🙂 jour des crêpes ou la chandeleur et encore des crêpes 🙂

oguruma · Mercredi à 12:23

Un complément

J'ai découvert il y a quelques temps en fouinant sur la toile...on n'y pense pas toujours....

ceci : #date(Date.Year(pDate ?? fnAujourdhui()),1,1)

ce symbole ?? vous permet d'éviter ce type de code
Valeur = if pDate is null then fnAujourdhui() else pDate

Cousinhub · Mercredi à 16:18

oguruma à dit:
Un complément

J'ai découvert il y a quelques temps en fouinant sur la toile...on n'y pense pas toujours....

ceci : #date(Date.Year(pDate ?? fnAujourdhui()),1,1)

ce symbole ?? vous permet d'éviter ce type de code
Valeur = if pDate is null then fnAujourdhui() else pDate

Bonjour,
Effectivement, cet opérateur est appelé de "coalescence"

Opérateur de coalescence

L’opérateur de coalescence ?? retourne le résultat de son opérande gauche s’il n’est pas null ; sinon, il retourne le résultat de son opérande droit. L’opérande de droite est évalué si et seulement si l’opérande de gauche est null.

Attention, il me semble qu'il n'est pas intégré à la version 2016 d'Excel... A vérifier...

oguruma · Mercredi à 18:29

Cousinhub à dit:
Bonjour,
Effectivement, cet opérateur est appelé de "coalescence"

Attention, il me semble qu'il n'est pas intégré à la version 2016 d'Excel... A vérifier...

Bonne remarque, je suis sur Office 365. J'ai un PC sur 2016... je ferai un test 🙂 merci

Effectuez une recherche sur Excel Downloads...

POWERQUERY :: LES DATES REMARQUABLES

oguruma

XLDnaute Occasionnel

Pièces jointes

oguruma

XLDnaute Occasionnel

Cousinhub

XLDnaute Barbatruc

Opérateur de coalescence

oguruma

XLDnaute Occasionnel

Discussions similaires

Nous accordons de l'importance à votre vie privée

POWERQUERY :: LES DATES REMARQUABLES

oguruma

XLDnaute Occasionnel

Pièces jointes

oguruma

XLDnaute Occasionnel

Cousinhub

XLDnaute Barbatruc

Opérateur de coalescence​

oguruma

XLDnaute Occasionnel

Discussions similaires

Nous accordons de l'importance à votre vie privée

Opérateur de coalescence