POWERQUERY :: Exemple simplifié de création de planning de réunions.. COMOP, COPIL, CODIR, COSUI, etc.

[oguruma]

A travers ce post démonstration d'une création de planning simplifié. L'idée est de générer une série de dates avec des événements répétitifs selon une certaines fréquence de manière à suivre ces événement sous un tableau Excel.

Description des éléments du planning

Exemple

Il suffit de créer un tableau en respectant cet ordre des colonnes. En revanche aucune importance sur le nom des colonnes. Ils sont libres. La fonction Pq les gère dynamiquement.
Cela fait référence au post https://excel-downloads.com/threads...onnes-dune-table-plusieurs-methodes.20080797/ à ce propos.

Plan de travail PowerQuery

La requête TB_PILOTAGE_DEV est là titre de tests de la fonction fnProjectPlanning
Appel de la fonction

let
    Source = fnProjectPlanning("TB_PILOTAGE")

in
    Source

Résultat

La fonction

let fnPlanning = (pTable as any ) as table =>

    let

        //-------------------------------------------------------------
        // Constante pour la colonne de planification
        //-------------------------------------------------------------
        COL_PLAN="Planification",

        //-------------------------------------------------------------
        // Identification de la source de données
        //-------------------------------------------------------------
        Source = if pTable is text then
                    Excel.CurrentWorkbook(){[Name=pTable]}[Content]
                    else if pTable is table then
                            pTable
                            else null,

        //-------------------------------------------------------------
        // Liste des colonnes de la tables
        //-------------------------------------------------------------
        LstCols=Table.ColumnNames(Source),
      
        //-------------------------------------------------------------
        // On extrait les noms des colonnes
        //-------------------------------------------------------------
        Ev_Nom=LstCols{0},
        Ev_Date=LstCols{1},
        Ev_Nbr=LstCols{2},
        Ev_Freq=LstCols{3},

        //-------------------------------------------------------------
        // Calculs indirects des noms de colonnes
        //-------------------------------------------------------------
        Nom="[" & Ev_Nom & "]",
        Dat="[" & Ev_Date & "]",
        Nbr= "[" & Ev_Nbr & "]",
        Freq= "[" & Ev_Freq & "]",

        //-------------------------------------------------------------
        // Evaluate de la colonne planning
        //-------------------------------------------------------------
        DateAddDays="Date.AddDays(" & Dat & ", " & Freq & ")",
        DurationFrom="Duration.From(" & Freq & ")",
        ListDates="each List.Dates( " & DateAddDays & ", " & Nbr & ", " & DurationFrom & " )",
        EvalListDates=Expression.Evaluate(ListDates, [List.Dates=List.Dates, Date.AddDays=Date.AddDays, Duration.From=Duration.From]),

        //-------------------------------------------------------------
        // Typage dynamique des colonnes en fonction des noms calculés
        //-------------------------------------------------------------
        TypeColumns = Table.TransformColumnTypes(Source,{{Ev_Nom, type text}, {Ev_Date, type date}, {Ev_Nbr, Int64.Type}, {Ev_Freq, Int64.Type}}),
        AddColumnPlanning = Table.AddColumn(TypeColumns, COL_PLAN, EvalListDates),

        //-------------------------------------------------------------
        // Présentation de la colonne planning
        //-------------------------------------------------------------
        ExpandPlanning = Table.ExpandListColumn(AddColumnPlanning, COL_PLAN),

        //-------------------------------------------------------------
        // Suppression des colonnes inutiles
        //-------------------------------------------------------------
        Planning = Table.RemoveColumns(ExpandPlanning,{Ev_Nbr, Ev_Freq})

    in
        Planning
in
    fnPlanning

Quelques remarques sur le code de la fonction
Pour ceux et celles qui souhaitent se lancer dans le langage M voici une manière de structurer le code. Certes critiquable... mais au moins nous avons un code lisible.

La fonction est capable d'identifier le type de la source

        Source = if pTable is text then

                    Excel.CurrentWorkbook(){[Name=pTable]}[Content]

                    else if pTable is table then

                            pTable

                            else null,

C'est ici que l'on détecte automatiquement le nom des colonnes. On les récupère via une liste et on les isole dans des variables spécifiques. (Voir la gestion des listes).

       //-------------------------------------------------------------
        // Liste des colonnes de la tables
        //-------------------------------------------------------------
        LstCols=Table.ColumnNames(Source),
      
        //-------------------------------------------------------------
        // On extrait les noms des colonnes
        //-------------------------------------------------------------
        Ev_Nom=LstCols{0},
        Ev_Date=LstCols{1},
        Ev_Nbr=LstCols{2},
        Ev_Freq=LstCols{3},

ça nous permet de gérer le type des colonnes de manière robuste quelque soit le nom des colonnes. Astuce à retenir pour les débutants.

        //-------------------------------------------------------------
        // Typage dynamique des colonnes en fonction des noms calculés
        //-------------------------------------------------------------
        TypeColumns = Table.TransformColumnTypes(Source,{{Ev_Nom, type text}, {Ev_Date, type date}, {Ev_Nbr, Int64.Type}, {Ev_Freq, Int64.Type}}),

Cette phase de code permet de gérer dynamiquement l'évaluation de la formule AddColumnPlanning = Table.AddColumn(TypeColumns, COL_PLAN, EvalListDates),

        //-------------------------------------------------------------
        // Calculs indirects des noms de colonnes
        //-------------------------------------------------------------
        Nom="[" & Ev_Nom & "]",
        Dat="[" & Ev_Date & "]",
        Nbr= "[" & Ev_Nbr & "]",
        Freq= "[" & Ev_Freq & "]",

Une manière ici de construite une instruction dynamiquement via Expression.Evaluate (INDIRECT sous Excel) - Cette fonction difficile à aborder pour les néophytes est très puissante. Elle permet de construire du code à la volée.

        //-------------------------------------------------------------
        // Evaluate de la colonne planning
        //-------------------------------------------------------------
        DateAddDays="Date.AddDays(" & Dat & ", " & Freq & ")",
        DurationFrom="Duration.From(" & Freq & ")",
        ListDates="each List.Dates( " & DateAddDays & ", " & Nbr & ", " & DurationFrom & " )",
        EvalListDates=Expression.Evaluate(ListDates, [List.Dates=List.Dates, Date.AddDays=Date.AddDays, Duration.From=Duration.From]),

Et on récupère le résultat de EvalListDates pour exécuter ce code

AddColumnPlanning = Table.AddColumn(TypeColumns, COL_PLAN, EvalListDates),

Résultat final sous Excel