POWERQUERY :: Renommage statique de colonnes - sélection de colonnes - données internes au classeur Excel

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
1708284892819.png


obtenir
1708284917336.png


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
1708285124777.png


Paramètres propres à l'application - au format Record PowerQuery
1708285173196.png


Environnement PowerQuery


1708285263155.png


1708285293332.png


Préparation de la table de renommage
1708285354578.png


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
    Custom1

Cette table et le code permettre de construire ce couple dans chaque élément de la liste. Exemple pour le 1er
1708285444856.png


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
1708285587779.png


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
    ColumnsKeep

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
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
    ToTableRename

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
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
   fnTbGetParamFromFileRecords

pour plus de compréhension voir le fichier joint
 

Pièces jointes

  • 1708285241393.png
    1708285241393.png
    12.4 KB · Affichages: 2
  • $__pqRenameColumnsNamesStatic_V1.zip
    141.4 KB · Affichages: 2
Dernière édition:

combory

XLDnaute Junior
Bonjour Oguruma,

Je suis une fois de plus impressionné par ce travail. Bravo.
Je n'ai pas encore "joué" avec ton fichier, mais j'ai eu ce message :

1708332212574.png


Dans POWERQUERY, il faut aller dans le menu fichier, options et paramètres, Options de requête, confidentialité et sélectionner "Toujours ignorer les paramètres de niveau de confidentialité" pour que ça fonctionne (après avoir modifié le fichier Paramètres.ini bien sûr).
Je suis sur Excel 2021. J'ai un message d'incompatibilité avec 365, mais ça ne pose de problème à priori.

Belle journée.
 

oguruma

XLDnaute Occasionnel
Bonjour combory, merci pour ce retour.
C'est vrai, j'aurai dû préciser ce petit réglage à faire comme tu l'évoques dans ton commentaire.
En fait, de mémoire ce réglage est à faire depuis la Excel 2016.... d'autant plus il n'est pas parlant et on pourrait être tenté de bidouillé par erreur son pare-feu à cause du message "firewall". Là-dessus je ne félicite pas $Krosoft pour ce manque de précision. Le message aurait pu clair demandant de modifier ces paramètres au lieu de mettre ce baratin incompréhensible ;)

Bon pour conclure, le développement c'est un jeu... j'ai environ 40 ans de code derrière moi dans plus de 10 langages dont des vieux de la vielle comme le PL1, GAP II ou encore plus ancien de chez ancien APL ;) bonne journée.
 
Dernière édition:

oguruma

XLDnaute Occasionnel
Je suis sur Excel 2021. J'ai un message d'incompatibilité avec 365, mais ça ne pose de problème à priori.
==> je l'ai de temps en temps même sur la reprise de mes propres dév sous O365...
je ne cherche même pas à comprendre car après avoir fait une modif+un save il disparaît. :)
 

Discussions similaires

Statistiques des forums

Discussions
313 277
Messages
2 096 766
Membres
106 746
dernier inscrit
acquabateaux