oguruma
XLDnaute Occasionnel
Bonjour,
En préambule ça complète ceci : https://excel-downloads.com/threads...onnes-dune-table-plusieurs-methodes.20080797/
La cible :
à partir de ces données dans le classeur
obtenir
On peut constater que les colonnes ont été renommées et certaines (pas toutes) ont été reprise
Comment ?
Dans ce post une nouvelle méthode pour renommer les colonnes, disons une variantes par rapport à celles qui ont déjà été présentées.
Organisation powerquery et des traitements
On passe déjà par deux fichiers paramètres : paramètres généraux - au format Record PowerQuery
Paramètres propres à l'application - au format Record PowerQuery
Environnement PowerQuery
Préparation de la table de renommage
Cette table et le code permettre de construire ce couple dans chaque élément de la liste. Exemple pour le 1er
Cet ensemble de couple Ancien / Nouveau sera utilisé pour renommer les colonnes.
On remarque que le code ne tient pas compte des noms de colonnes. Elles sont donc totalement libres. Cela éviter de planter.
Préparation de la table de sélection des colonnes
Idem nous ne sommes pas dépendants des noms de colonnes en dehors de la colonne [ACTIF] qui indique si la colonne doit être retenue (cf. fichier de paramètres)
Comme on peut le voir on ne retient que les colonnes positionnées à OUI : FilterSelect = Table.SelectRows(Source, each [ACTIF] = "OUI"),
Production de la table finale
On remarque que le code est sécurisé au niveau de l'appel des tables soit via powerquery soit via excel. Pour éviter tout plantage sur les noms de colonnes manquants : MissingField.Ignore.
Si cumul des échecs pendant les traitements on renvoie une table vide pour ne pas planter totalement : otherwise #table({},{})
Les fonctions annexes
pour plus de compréhension voir le fichier joint
En préambule ça complète ceci : https://excel-downloads.com/threads...onnes-dune-table-plusieurs-methodes.20080797/
La cible :
à partir de ces données dans le classeur
obtenir
On peut constater que les colonnes ont été renommées et certaines (pas toutes) ont été reprise
Comment ?
Dans ce post une nouvelle méthode pour renommer les colonnes, disons une variantes par rapport à celles qui ont déjà été présentées.
Organisation powerquery et des traitements
On passe déjà par deux fichiers paramètres : paramètres généraux - au format Record PowerQuery
Paramètres propres à l'application - au format Record PowerQuery
Environnement PowerQuery
Préparation de la table de renommage
VB:
let
Source = try TB_PARAMS_APPLI
otherwise Excel.CurrentWorkbook(){[Name="TB_PARAMS_APPLI"]}[Content],
ListColumns=Table.ColumnNames(Source),
ListColumnsMatch=List.FirstN(ListColumns,2),
SelectColumns = Table.SelectColumns(Source,ListColumnsMatch),
Custom1 = List.Zip( Table.ToColumns( SelectColumns ) )
in
Custom1Cette table et le code permettre de construire ce couple dans chaque élément de la liste. Exemple pour le 1er
Cet ensemble de couple Ancien / Nouveau sera utilisé pour renommer les colonnes.
On remarque que le code ne tient pas compte des noms de colonnes. Elles sont donc totalement libres. Cela éviter de planter.
Préparation de la table de sélection des colonnes
PowerQuery:
let
Source = try TB_PARAMS_APPLI
otherwise Excel.CurrentWorkbook(){[Name="TB_PARAMS_APPLI"]}[Content],
FilterSelect = Table.SelectRows(Source, each [ACTIF] = "OUI"),
ListColumns=Table.ColumnNames(FilterSelect),
ListColumnsKeep=List.FirstN(ListColumns,1),
ColumnsKeep=Table.Column(FilterSelect, ListColumnsKeep{0})
in
ColumnsKeepIdem nous ne sommes pas dépendants des noms de colonnes en dehors de la colonne [ACTIF] qui indique si la colonne doit être retenue (cf. fichier de paramètres)
Comme on peut le voir on ne retient que les colonnes positionnées à OUI : FilterSelect = Table.SelectRows(Source, each [ACTIF] = "OUI"),
Production de la table finale
PowerQuery:
let
//---------------------------------------------------------------------------------------
// Requête pivot pour chagrer les données à traiter
//---------------------------------------------------------------------------------------
//---------------------------------------------------------------------------------------
// Lecture des paramètres
// La table est soit dans l'environnement PowerQuery soit dans une table Excel (TS)
//---------------------------------------------------------------------------------------
TbData = try fnGetParameter(TB_PARAMS_GENRAUX,"TB_DATA")
otherwise fnGetParameter("TB_PARAMS_GENRAUX","TB_DATA"),
//---------------------------------------------------------------------------------------
// Chargement des données - chemin lu à partir de la table des paramètres
//---------------------------------------------------------------------------------------
Source = try Excel.CurrentWorkbook(){[Name=TbData]}[Content]
otherwise try TbData // si la table n'existe pas on renvoie une vide
otherwise #table({},{}),
//---------------------------------------------------------------------------------------
// Quelles sont les colonnes à conserver ?
// MissingField.Ignore : pour éviter que la requête plante si les colonnes sont inconnues
//---------------------------------------------------------------------------------------
SourceKeep = try Table.SelectColumns(Source,ListSelectColumns,MissingField.Ignore)
otherwise #table({},{}),
//---------------------------------------------------------------------------------------
// Renommage des colonnes
//---------------------------------------------------------------------------------------
ToTableRename = try Table.RenameColumns(SourceKeep,ListRenameColumns,MissingField.Ignore)
otherwise #table({},{})
in
ToTableRenameOn remarque que le code est sécurisé au niveau de l'appel des tables soit via powerquery soit via excel. Pour éviter tout plantage sur les noms de colonnes manquants : MissingField.Ignore.
Si cumul des échecs pendant les traitements on renvoie une table vide pour ne pas planter totalement : otherwise #table({},{})
Les fonctions annexes
PowerQuery:
let fnGetParameter =
(
pTable as any,
pName as text,
optional pParam as text,
optional pVal as text
) =>
let
ParamSource = if pTable is text then
Excel.CurrentWorkbook(){[Name=pTable]}[Content]
else pTable,
Parametre = if pParam <> null then
pParam
else "PARAMETRE",
Valeur = if pVal <> null then
pVal
else "VALEUR",
StrParam="each ([" & Parametre & "]) = " & """" & pName & """",
EvalParam=Expression.Evaluate(StrParam,[ParamSource=ParamSource]),
ParamRow = Table.SelectRows(ParamSource, EvalParam),
Value= if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},Valeur)
in
//Parametre
Value
in
fnGetParameter
/* Exemple
{
[ PARAMETRE = "PARAM1", VALEUR = "STRING1" ] ,
[ PARAMETRE = "PARAM2", VALEUR = "STRING2" ] ,
[ PARAMETRE = "PARAM4", VALEUR = 25 ] ,
[ PARAMETRE = "PARAM5", VALEUR = "16/10/2023" ] ,
[ PARAMETRE = "PARAM6", VALEUR = null]
},
type table[PARAMETRE = Text.Type, VALEUR = Any.Type ]
*/
let
fnTbGetParamFromFileRecords = (
optional ppFileParam as text,
optional ppExtension as text,
optional ppFolder as text,
optional ppDelimiter as text,
optional ppColParam as text,
optional ppColVal as text
) =>
let
//---------------------------------------------------------------------------------------
// Sous-dossier où sont stockés les fichiers paramètres
//---------------------------------------------------------------------------------------
SUBFOLDER_PARAMS="Params",
BACK_SLASH="\",
//---------------------------------------------------------------------------------------
// Lecture du dossier courants - Nom de la plage nommée DOSSIER_COURANT
//---------------------------------------------------------------------------------------
CurrentDirectory = Excel.CurrentWorkbook(){[Name="DOSSIER_COURANT"]}[Content]{0}[Column1],
//---------------------------------------------------------------------------------------
// Calcul des différents accès aux données
//---------------------------------------------------------------------------------------
PARAM_PATH_DEFAULT=CurrentDirectory & BACK_SLASH & SUBFOLDER_PARAMS,
PARAM_EXTENSION_DEFAULT = ".ini",
PARAM_DELIMITER_DEFAULT = "=",
DEFAULT = PARAM_PATH_DEFAULT & "\",
//---------------------------------------------------------------------------------------
// Noms des colonnes par défaut pour la table de paramètres
//---------------------------------------------------------------------------------------
COL_PARAMETRE="PARAMETRE",
COL_VALEUR="VALEUR",
//---------------------------------------------------------------------------------------
// Par défaut la table s'appelera Parametres
//---------------------------------------------------------------------------------------
pParam = if ppFileParam <> null then
ppFileParam
else "Parametres",
//---------------------------------------------------------------------------------------
// Par défaut l'extension du fichier paramètre sera .ini
//---------------------------------------------------------------------------------------
Extension = if ppExtension is null then
PARAM_EXTENSION_DEFAULT
else ppExtension,
//---------------------------------------------------------------------------------------
// Dossier par défaut
//---------------------------------------------------------------------------------------
PathInitial = if ppFolder <> null then
ppFolder
else DEFAULT,
//---------------------------------------------------------------------------------------
// Séparateur par défaut
//---------------------------------------------------------------------------------------
Delimiter = if ppDelimiter <> null then
ppDelimiter
else PARAM_DELIMITER_DEFAULT,
//---------------------------------------------------------------------------------------
// Colonne paramètre par défaut
//---------------------------------------------------------------------------------------
Parametre = if ppColParam <> null then
ppColParam else
COL_PARAMETRE,
//---------------------------------------------------------------------------------------
// Colonne valeur du paramètre par défaut
//---------------------------------------------------------------------------------------
Valeur = if ppColVal <> null then
ppColVal else
COL_VALEUR,
//---------------------------------------------------------------------------------------
// Chemin du module à charger
//---------------------------------------------------------------------------------------
PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
FileParam = PathProcedure & pParam & Extension,
//---------------------------------------------------------------------------------------
// Lecture du fichier paramètres
//---------------------------------------------------------------------------------------
Source1 = Text.FromBinary(Binary.Buffer(File.Contents(FileParam))),
Source2 = "Table.FromRecords ( " & Source1 & " )",
ToTable = Expression.Evaluate(Source2, #shared)
in
ToTable
in
fnTbGetParamFromFileRecordspour plus de compréhension voir le fichier joint
Dernière édition: