oguruma
XLDnaute Occasionnel
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
Résultat
La fonction
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
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).
ç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.
Cette phase de code permet de gérer dynamiquement l'évaluation de la formule AddColumnPlanning = Table.AddColumn(TypeColumns, COL_PLAN, EvalListDates),
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.
Et on récupère le résultat de EvalListDates pour exécuter ce code
Résultat final sous 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
PowerQuery:
let
Source = fnProjectPlanning("TB_PILOTAGE")
in
SourceRésultat
La fonction
PowerQuery:
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
fnPlanningQuelques 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
PowerQuery:
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).
PowerQuery:
//-------------------------------------------------------------
// 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.
PowerQuery:
//-------------------------------------------------------------
// 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),
PowerQuery:
//-------------------------------------------------------------
// 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.
PowerQuery:
//-------------------------------------------------------------
// 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
PowerQuery:
AddColumnPlanning = Table.AddColumn(TypeColumns, COL_PLAN, EvalListDates),Résultat final sous Excel