Discussion :

[LG-69]

Bonjour,

J'espère que je poste au bon endroit, je n'ai pas trouvé de forum spécifique pour Power Query que j'utilise dans excel.
Mon besoin : je cherche à calculé le nombre de jours ouvrés entre 2 dates dans Power Query.

1ère solution : J'ai trouvé une solution en générant à la volée une liste des dates entre mes deux dates (début et fin) puis en retirant les samedis et dimanches, puis en retirant les jours "de fermeture" qui sont stockés dans une liste externe puis je compte le nombre d'éléments restant dans ma liste. ca marche mais au niveau performance c'est pas top, ça mouline un moment.... (34000 lignes avec autant de fois le calcul)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
(DateDebut as date, DateFin as date) as number =>
let 
    ListeDates = List.Dates(DateDebut,Number.From(DateFin-DateDebut),#duration(1,0,0,0)),
    RetireWeekEnd = List.Select(ListeDates, each Date.DayOfWeek(_,Day.Monday)<5),
    RetireJoursfériés = List.RemoveItems(RetireWeekEnd, Tabl_jours_fériés),
    CompteJours = List.Count(RetireJoursfériés)
in
CompteJours

Autre solution : J'ai pensé à un autre moyen peut-être plus performant mais je sèche un peu pour le finaliser
- J'ai une table (Calendrier des jours ouvrés) générée par script M avec les dates et un top 0/1 pour dire si c'est ouvré ou non

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= Table.AddColumn(OrdreCol, "Ouvre", each if [Num du jour Sem] >= 6 or List.Contains(Tabl_jours_fériés,[Date])=true then 0 else 1)

- J'ai écrit une fonction que je vais appeler avec 2 dates (date de début et date de fin)
- Je génère à la volée une liste des dates entre mes deux bornes
- Je transforme la liste en table
- Je joints ma table à la table "Calendrier des jours ouvrés" sur la clé Date de chacune des tables pour compléter ma première table (celle générée à la volée) du top 1/0 disant si c'est ouvré ou non
- J'agrège le résultat en sommant le top 1/0
=> ça doit me donner le nombre de jours ouvrés entre mes deux dates

J'ai écrit ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
let
    Source = (DateDebut as date, DateFin as date) as number =>
let 
    ListeDates = List.Dates(DateDebut,Number.From(DateFin-DateDebut),#duration(1,0,0,0)),
    Tb_Date = Table.FromList(ListeDates),
    Tb_jointe = Table.NestedJoin(Tb_Date, "Column1", #"Calendrier des jours ouvrés", "Date", "Ouvre", JoinKind.LeftOuter),
    CompteJours = Table.AggregateTableColumn(Tb_jointe, {"Ouvre", List.Sum})
in
CompteJours
in
    Source

et ça marche pas... pas d'erreur de syntaxe mais quand je lance il me dit qu'il arrive pas à convertir ma 1ère date en Texte (je sais pas pourquoi il veut faire ça)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Une erreur s'est produite dans la requête « Requête1 ». Expression.Error : Désolé... Nous n'avons pas pu convertir la valeur #date(2022, 3, 1) en type Text.
Détails :
    Value=01/03/2022
    Type=[Type]

Toute aide serait la bienvenue.

Je précise que je suis débutant en M, j'ai juste cherché du code à droite à gauche sur le net, et sur l'aide Microsoft, du coup je passe peut-être à coté de quelque chose, mais je ne demande qu'à apprendre.

Merci à vous.

[Raccourcix]

Bonjour

en ligne 5, il faut ajouter le 2d argument Tb_Date = Table.FromList(ListeDates, Splitter.SplitByNothing()),
par ailleurs, le mieux me semble t-il est de conserver dans votre table Calendrier des jours ouvrés uniquement les lignes à 1 (donc seulement les jours ouvrés que vous voulez dénombrer)

Ainsi ensuite vous n'avez qu'à faire une jointure uniquement les lignes en correspondance JoinKind.Inner et à compter les lignes ( car pour des questions de performance il faut éviter la fonction Table.AggregateTableColumn)

et il doit manquer un +1 dans la durée Fin-Début

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
(DateDebut as date, DateFin as date) =>
let 
    ListeDates = List.Dates(DateDebut,Number.From(DateFin-DateDebut)+1,#duration(1,0,0,0)),
    Tb_Date = Table.FromList(ListeDates, Splitter.SplitByNothing()),
    Tb_jointe = Table.NestedJoin(Tb_Date, "Column1", #"Calendrier des jours ouvrés", "Date", "Ouvre", JoinKind.Inner),
    CompteJours = Table.RowCount(Tb_jointe)
in
CompteJours

Stéphane

[Raccourcix]

Autre possibilité

vous créez une liste des jours ouvrés de votre période (du 1er janvier au 31 décembre par exemple).

et dans votre table vous ajoutez une colonne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

List.Count(List.Intersect({List.Dates([DateDebut],Number.From([DateFin]-[DateDebut])+1,#duration(1,0,0,0)),#"Liste jours ouvrés"}))

Pour améliorer la performance il est possible d'utiliser le buffer pour mettre en mémoire votre calendrier des jours ouvrés (en liste ou en table) avec List.Buffer ou Table.Buffer


Stéphane

[LG-69]

Merci beaucoup Raccourcix pour toutes ces pistes :

1ère solution qui marche mais reste longue à l'actualisation (> 1 min)
Je génère mon calendrier et ne garde en liste que les jours ouvrés (super idée ça, merci) et la transforme en liste

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
let
    // Ce script vous est proposé par Daxone.fr modifié par LG
    // pour changer la date de début, modifiez le code ci-dessous
    // par exemple, un début au 1er janvier 2020 s’écrit #date(2020, 1, 1)
    Debut = #date(2020, 1, 1),
    /* Positionne la date de fin au 31/12 de l'année en cours */
    Fin = #date(Date.Year(DateTime.Date(DateTime.LocalNow())), 12, 31),
    Length = Duration.Days(Fin-Debut),
    /* création de la table calendrier */
    Source = List.Dates(Debut, Length, #duration(1, 0, 0, 0)),
    /* transforme la liste en table */
    EnTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ColRenom = Table.RenameColumns(EnTable,{{"Column1", "Date"}}),
    ChangeType = Table.TransformColumnTypes(ColRenom,{{"Date", type date}}),
    /* Ajoute le top jour ouvré (1) / jour non ouvré (0) suivant si c'est un week-end ou si il est présent dans la liste des jours fériés */
    #"Jours ouvrés" = Table.AddColumn(ChangeType, "Ouvre", each if Date.DayOfWeek(([Date]), Day.Monday)+1 >= 6 or List.Contains(Tabl_jours_fériés,[Date])=true then 0 else 1),
    /* Ne garde que les jours ouvrés */
    #"Lignes filtrées" = Table.SelectRows(#"Jours ouvrés", each ([Ouvre] = 1)),
    Date = #"Lignes filtrées"[Date]
in
    Date

Création de la fonction faisant l'intersection de la liste des jours compris entre mes deux dates et la liste des jours ouvrés

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
(DateDebut as date, DateFin as date) as number =>
let 
    /* génère la liste des jours entre date de début et date de fin */
    ListeDates = List.Dates(DateDebut,Number.From(DateFin-DateDebut),#duration(1,0,0,0)),
    /* récupère l'intersection avec la liste des jours ouvrés */
    ListeDatesOuvrees = List.Intersect({ListeDates,#"Calendrier des jours ouvrés"}),
    /* compte le nombre d'éléments dans la liste restante */
    CompteJours = List.Count(ListeDatesOuvrees)
in
    CompteJours

Il ne reste plus qu'à appeler la fonction à chaque fois que nécessaire

2ème solution qui me semble un peu plus rapide
Je génère mon calendrier et ne garde en liste que les jours ouvrés (super idée ça, merci) et la garde en table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
let
    // Ce script vous est proposé par Daxone.fr modifié par LG
    // pour changer la date de début, modifiez le code ci-dessous
    // par exemple, un début au 1er janvier 2020 s’écrit #date(2020, 1, 1)
    Debut = #date(2020, 1, 1),
    /* Positionne la date de fin au 31/12 de l'année en cours */
    Fin = #date(Date.Year(DateTime.Date(DateTime.LocalNow())), 12, 31),
    Length = Duration.Days(Fin-Debut),
    /* création de la table calendrier */
    Source = List.Dates(Debut, Length, #duration(1, 0, 0, 0)),
    /* transforme la liste en table */
    EnTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ColRenom = Table.RenameColumns(EnTable,{{"Column1", "Date"}}),
    ChangeType = Table.TransformColumnTypes(ColRenom,{{"Date", type date}}),
    /* Ajoute le top jour ouvré (1) / jour non ouvré (0) suivant si c'est un week-end ou si il est présent dans la liste des jours fériés */
    #"Jours ouvrés" = Table.AddColumn(ChangeType, "Ouvre", each if Date.DayOfWeek(([Date]), Day.Monday)+1 >= 6 or List.Contains(Tabl_jours_fériés,[Date])=true then 0 else 1),
    /* Ne garde que les jours ouvrés */
    #"Lignes filtrées" = Table.SelectRows(#"Jours ouvrés", each ([Ouvre] = 1))
in
    #"Lignes filtrées"

Création de la fonction faisant la jointure des jours compris entre mes deux dates et la table des jours ouvrés

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
(DateDebut as date, DateFin as date) as number =>
let 
    ListeDates = List.Dates(DateDebut,Number.From(DateFin-DateDebut),#duration(1,0,0,0)),
    Tb_Date = Table.FromList(ListeDates, Splitter.SplitByNothing()),
    Tb_jointe = Table.NestedJoin(Tb_Date, "Column1", #"Table_Calendrier des jours ouvrés", "Date", "Ouvre", JoinKind.Inner),
    CompteJours = Table.RowCount(Tb_jointe)
in
    CompteJours

Il ne reste plus qu'à appeler la fonction à chaque fois que nécessaire

Me reste à explorer 2 pistes :
- en générant mon calendrier, calculer le nombre de jours ouvrés à partir d'une référence pour chaque jour du calendrier (genre un index ?) puis rechercher l'index de mon jour de fin et de mon jour début et faire la différence et là ça devrait être pas mal au niveau perf !
- charger en buffer suivant vos conseils Raccourcix mais là j'ai pas tout compris comment ça fonctionne (en gros quoi charger en buffer ? Mon calendrier des jours ouvrés ? ou ma liste de jours entre 2 dates ce qui ne me semble pas possible comme ça change tout le temps.

[Raccourcix]

- en générant mon calendrier, calculer le nombre de jours ouvrés à partir d'une référence pour chaque jour du calendrier (genre un index ?) puis rechercher l'index de mon jour de fin et de mon jour début et faire la différence et là ça devrait être pas mal au niveau perf !

Très bonne idée, deux fusions et une différence, c'est sûrement plus rapide

- charger en buffer suivant vos conseils Raccourcix mais là j'ai pas tout compris comment ça fonctionne (en gros quoi charger en buffer ? Mon calendrier des jours ouvrés ? ou ma liste de jours entre 2 dates ce qui ne me semble pas possible comme ça change tout le temps.

C'est la liste des jours ouvrés qu'il faut mettre en mémoire avec List.Buffer
Dans ce cas, la création d'une fonction personnalisée n'est pas utile.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Vos étapes....,
Liste_JO = List.Buffer(#"Liste des jours ouvrés"),
Ajout_Colonnes=Table.AddColumn(Etape_précédente,"Nb JO", each List.Count(List.Intersect({List.Dates([DateDebut],Number.From([DateFin]-[DateDebut])+1,#duration(1,0,0,0)),Liste_JO}))),

enfin, j'insiste sur le +1 dans le calcul Fin-Debut pour la création de la plage de date.

Stéphane

[LG-69]

Bonjour,

Merci beaucoup pour ces nouveaux retours.

- Solution du Buffer : je l'ai implémentée grâce à vos conseils, ça marche

- Solution de l'index : je l'ai implémentée et c'est clairement le plus rapide par contre il me reste un souci à régler. En effet mes dates de début tombent parfois des week-end !! et donc je n'ai pas la date dans mon calendrier indexé comme je ne garde que les jours ouvrés. C'est un cas qui peut exister car je gère des tickets d'incident et rien n'empêche quelqu'un de le saisir le samedi même si c'est sensé ne pas être un jour ouvré, le délai entre le début et la fin doit par contre ne compter que les jours ouvrés... du coup il me faudrait garder toutes les dates dans mon calendrier mais n'incrémenter l'index que si le jour est ouvré mais ça pour le moment je ne sais pas faire "valeur de la ligne précédente + 1 si ouvrés, +0 si non ouvrés" (à par peut-être faire un MAX des valeurs de la colonne + 1 mais là niveau performance ce sera pourri de recalculer le MAX à chaque ligne) => je suis preneur de pistes. J'ai trouvé des choses sur internet mais tout en anglais et du coup j'ai pas trop compris pour le moment.

Même si j'ai à présent 3 solutions qui fonctionnent (mais pas forcément top en performances), je souhaiterai finaliser cette dernière (avec index personnalisés) car elle me paraît la plus performante et aussi il y aura ainsi toutes les solutions dans ce poste ce qui pourra servir à la communauté.

[Raccourcix]

- Solution de l'index : je l'ai implémentée et c'est clairement le plus rapide par contre il me reste un souci à régler. En effet mes dates de début tombent parfois des week-end !! et donc je n'ai pas la date dans mon calendrier indexé comme je ne garde que les jours ouvrés. C'est un cas qui peut exister car je gère des tickets d'incident et rien n'empêche quelqu'un de le saisir le samedi même si c'est sensé ne pas être un jour ouvré, le délai entre le début et la fin doit par contre ne compter que les jours ouvrés... du coup il me faudrait garder toutes les dates dans mon calendrier mais n'incrémenter l'index que si le jour est ouvré mais ça pour le moment je ne sais pas faire "valeur de la ligne précédente + 1 si ouvrés, +0 si non ouvrés" (à par peut-être faire un MAX des valeurs de la colonne + 1 mais là niveau performance ce sera pourri de recalculer le MAX à chaque ligne) => je suis preneur de pistes. J'ai trouvé des choses sur internet mais tout en anglais et du coup j'ai pas trop compris pour le moment.

le plus simple : vous faites deux étapes
- d'un coté les jours ouvrés (filtre =1) et vous ajoutez l'index
- de l'autre les jours non ouvrés (filtre = 0)

et vous faites l'union des deux bases (Table.Combine) comme ça vous récupérez toutes les dates.

Stéphane

[LG-69]

Merci beaucoup pour cette piste.
Ci-dessous ce que j'ai fait, ça marche et en plus le temps de réponse est très rapide !

J'ai ma table calendrier générée par script avec indication jours ouvrées/non ouvrés

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
let
    // Ce script vous est proposé par Daxone.fr modifié par LG
    // pour changer la date de début, modifiez le code ci-dessous
    // par exemple, un début au 1er janvier 2020 s’écrit #date(2020, 1, 1)
    Debut = #date(2020, 1, 1),
    /* Positionne la date de fin au 31/12 de l'année en cours */
    Fin = #date(Date.Year(DateTime.Date(DateTime.LocalNow())), 12, 31),
    Length = Duration.Days(Fin-Debut),
    /* création de la table calendrier */
    Source = List.Dates(Debut, Length, #duration(1, 0, 0, 0)),
    /* transforme la liste en table */
    EnTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ColRenom = Table.RenameColumns(EnTable,{{"Column1", "Date"}}),
    ChangeType = Table.TransformColumnTypes(ColRenom,{{"Date", type date}}),
    /* Ajoute le top jour ouvré (1) / jour non ouvré (0) suivant si c'est un week-end ou si il est présent dans la liste des jours fériés */
    #"Jours ouvrés" = Table.AddColumn(ChangeType, "Ouvre", each if Date.DayOfWeek(([Date]), Day.Monday)+1 >= 6 or List.Contains(Tabl_jours_fériés,[Date])=true then 0 else 1)
in
    #"Jours ouvrés"

J'ai une table des jours ouvrés uniquement indexée basée sur un alias de la table précédente :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
let
    Source = Table_Calendrier,
    #"Lignes filtrées" = Table.SelectRows(Source, each ([Ouvre] = 1)),
    #"Index ajouté" = Table.AddIndexColumn(#"Lignes filtrées", "Index", 1, 1),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"Index ajouté",{"Date", "Index"})
in
    #"Autres colonnes supprimées"

J'ai une nouvelle table de tous les jours pour laquelle je vais aller chercher le numéro d'index dans la table précédente (donc que les jours ouvrés, les autres auront "null"), je trie par date croissante (la fusion m'a mis un peu le désordre) puis je remplis vers le haut l'index (les null sont remplacés par le numéro d'index d'après) si bien qu'une création de mon ticket le samedi ou le dimanche sera considérée comme une création le lundi suivant (c'est ce que je veux)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
let
    Source = Table_Calendrier,
    #"Requêtes fusionnées" = Table.NestedJoin(Source, {"Date"}, Table_Calendrier_ouvrés, {"Date"}, "Table_Calendrier_ouvrés", JoinKind.FullOuter),
    #"Table_Calendrier_ouvrés développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Table_Calendrier_ouvrés", {"Index"}, {"Index"}),
    #"Lignes triées" = Table.Sort(#"Table_Calendrier_ouvrés développé",{{"Date", Order.Ascending}}),
    #"Rempli vers le haut" = Table.FillUp(#"Lignes triées",{"Index"}),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"Rempli vers le haut",{"Date", "Index"})
in
    #"Autres colonnes supprimées"

Dans ma table final il ne me reste plus qu'à fusionner ma table sur la clé "date de début" et de récupérer l'index, puis pareil sur la date de fin, puis de faire la différence entre les deux. J'ai fait en étapes mais je peux aussi tout faire en une seule fois

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
let
    .......
    #"Requêtes fusionnées" = Table.NestedJoin(#"Type modifié", {"Date CREA"}, Table_Calendrier_indexé, {"Date"}, "Table_Calendrier_indexé", JoinKind.LeftOuter),
    #"Table_Calendrier_indexé développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Table_Calendrier_indexé", {"Index"}, {"Index date début"}),
    #"Requêtes fusionnées1" = Table.NestedJoin(#"Table_Calendrier_indexé développé", {"Date RESOL"}, Table_Calendrier_indexé, {"Date"}, "Table_Calendrier_indexé", JoinKind.LeftOuter),
    #"Table_Calendrier_indexé développé1" = Table.ExpandTableColumn(#"Requêtes fusionnées1", "Table_Calendrier_indexé", {"Index"}, {"Index date fin"}),
    #"Soustraction insérée" = Table.AddColumn(#"Table_Calendrier_indexé développé1", "Delta", each [Index date fin] - [Index date début], type number)
in
    #"Soustraction insérée"

Je crois que je tiens LA solution après pas mal de recherches, de tâtonnements et petit à petit une meilleure compréhension du fonctionnement du langage M, merci beaucoup Raccourcix !