POWERQUERY :: Renommer les colonnes d'une table - Plusieurs méthodes

[oguruma]

Ce post a pour but de démontrer qu'il n'y a pas une seule et unique méthode pour renommer les colonnes d'une table dynamiquement. Les méthodes ici présentées ne sont certes pas exhaustives.
Le second objectif de ce post est aussi de montrer les diverses capacités et techniques pour développer ses propres fonctions en PowerQuery ainsi que quelques astuces de programmation en langage M mettant en oeuvre des fonction peu connues. Ces techniques seront certainement d'une grande utilité pour ceux ou celles qui veulent se lancer dans le langage M.

Source des données exemples

Nous avons deux tables :
- une qui contient des colonnes à renommer
- une qui correspond à une table de correspondance pour le renommage des colonnes

Inventaires des requêtes et fonctions PowerQuery présentées

Table de correspondance TB_RENOM

Table des données TB_Source

Méthode 1
On passe par le classique List.Zip

let fnRename = (pSource as any, pSourceRen as any ) as table =>
    let
        Source=if pSource is text then
        Excel.CurrentWorkbook(){[Name=pSource]}[Content]
        else pSource,

        SourceRen=if pSourceRen is text then
        Excel.CurrentWorkbook(){[Name=pSourceRen]}[Content]
        else pSourceRen,

        LstOldNames=Table.ColumnNames(Source),
        LstTblRename=Table.ColumnNames(SourceRen),

        LstNewNames=Expression.Evaluate("SourceRen[" & LstTblRename{1} & "]",[SourceRen=SourceRen]),

        RenameColumnsV1=Table.RenameColumns( Source,
                        List.Zip( { LstOldNames,LstNewNames } ),
                        MissingField.Ignore )                       
    in
        RenameColumnsV1
in
    fnRename

LstNewNames=Expression.Evaluate("SourceRen[" & LstTblRename{1} & "]",[SourceRen=SourceRen]),
Cette ligne permet de gérer dynamiquement le nom de la colonne cible de correspondance sans ce soucier du nom de la colonne. Expression.Evaluate c'est en quelques sortes l'équivalent de la fonction INDIRECT sous Excel.

On récupère les anciens noms directement dans la table par cette ligne : LstOldNames=Table.ColumnNames(Source),
Puis la fonction List.Zip permet d'établir la correspondance entre les anciens et les nouveaux noms : List.Zip( { LstOldNames,LstNewNames } ),
Méthode 1 - améliorée
On ne va plus cherche les anciens noms dans la table des données mais dans la table de correspondance

let fnRename = (pSource as any, pSourceRen as any ) as table =>
    let
        Source=if pSource is text then
        Excel.CurrentWorkbook(){[Name=pSource]}[Content]
        else pSource,

        SourceRen=if pSourceRen is text then
        Excel.CurrentWorkbook(){[Name=pSourceRen]}[Content]
        else pSourceRen,

        LstTblRename=Table.ColumnNames(SourceRen),   

        LstOldNames=Expression.Evaluate("SourceRen[" & LstTblRename{0} & "]",[SourceRen=SourceRen]),
        LstNewNames=Expression.Evaluate("SourceRen[" & LstTblRename{1} & "]",[SourceRen=SourceRen]),

        RenameColumnsV1=Table.RenameColumns( Source,
                        List.Zip( { LstOldNames,LstNewNames } ),
                        MissingField.Ignore )                       
    in
        RenameColumnsV1
in
    fnRename

Le calcul de la colonne à renommer est effectué de la même manière que la méthode 1 ci-dessus.

Méthode 2
Cette méthode comme les suivantes est plus étoffée et on ne passe pas par une table de correspondance. On va là intervenir sur la casse des noms de colonnes et le remplacement de caractères.

//-------------------------------------------------------------------------------------------------------------------------
// Renommage des colonnes d'une table avec remplacement de caractères et modification de la casse
//-------------------------------------------------------------------------------------------------------------------------

let fnConvertTableColumnsNames = (pSource as any,pStrFrom as text, pStrTo as text, optional pCase as text) as table =>   
    let
        //------------------------------------------------------------------------
        // Identification de la source
        //------------------------------------------------------------------------
        pTable=if pSource is text then
                Excel.CurrentWorkbook(){[Name=pSource]}[Content]
                else pSource,       

        //------------------------------------------------------------------------
        // Remplacement des caractères demandés
        //------------------------------------------------------------------------
        TransformSource = Table.RenameColumns(pTable
                        ,List.Transform(Table.ColumnNames(pTable),
                                        each {_, Text.Replace(_, pStrFrom, pStrTo)}
                                        )
                        ),

        //------------------------------------------------------------------------
        // Remplacement de la casse
        //------------------------------------------------------------------------
        CaseSource = if Text.Upper(pCase) = "U" then
                        Table.TransformColumnNames(TransformSource, each Text.Upper(_))
                        else if Text.Upper(pCase) = "L" then
                                Table.TransformColumnNames(TransformSource, each Text.Lower(_))
                             else if Text.Upper(pCase) = "P" then
                                  Table.TransformColumnNames(TransformSource, each Text.Proper(_))
                                else TransformSource
    in
        CaseSource
in
    fnConvertTableColumnsNames

Même principe mais suppression d'une étape qui est remplacée par l'appel d'une fonction interne

//-------------------------------------------------------------------------------------------------------------------------
// Renommage des colonnes d'une table avec remplacement de caractères et modification de la casse
//-------------------------------------------------------------------------------------------------------------------------

let fnConvertTableColumnsNames = (pSource as any,pStrFrom as text, pStrTo as text, optional pCase as text) as table =>   
    let

        //-------------------------------------------------------------------
        // Construction d'une fonction chargée du remplacement de la casse
        //-------------------------------------------------------------------
        FunctionReplaceCase= (pElem as text) =>
            let
                ConvertCase=if Text.Upper(pCase) = "U" then
                    Text.Upper(pElem)
                    else if Text.Upper(pCase) = "L" then
                            Text.Lower(pElem)
                            else if Text.Upper(pCase) = "P" then
                                    Text.Proper(pElem)
                                    else null,
            Replace=Text.Replace(ConvertCase, pStrFrom, pStrTo)
            in
                Replace,   

        //-------------------------------------------------------------------
        // Source des données
        //-------------------------------------------------------------------
        pTable=if pSource is text then
                Excel.CurrentWorkbook(){[Name=pSource]}[Content]
                else pSource,   
        
        //-----------------------------------------------------------------------------
        // Renommage des colonnes et appel de la fonction pour la gestion de la casse
        //-----------------------------------------------------------------------------
        TransformSource = Table.RenameColumns(pTable
                        ,List.Transform(Table.ColumnNames(pTable),
                                        //-------------------------------------------------------------------
                                        // Appel de la fonction et passage en paramètre le nom de la colonne
                                        //-------------------------------------------------------------------
                                        each {_, FunctionReplaceCase(_)}
                                        )
                        )

    in
        TransformSource
in
    fnConvertTableColumnsNames

La fonction interne est celle-ci
FunctionReplaceCase= (pElem as text) =>
let
ConvertCase=if Text.Upper(pCase) = "U" then
Text.Upper(pElem)
else if Text.Upper(pCase) = "L" then
Text.Lower(pElem)
else if Text.Upper(pCase) = "P" then
Text.Proper(pElem)
else null,
Replace=Text.Replace(ConvertCase, pStrFrom, pStrTo)
in
Replace,

Méthode 3 - elle donne les mêmes résultats mais elle est plus découpée en étapes afin d'avoir la main sur toutes les phases de transformation

//-------------------------------------------------------------------------------------------------------------------------
// Renommage des colonnes d'une table avec remplacement de caractères et modification de la casse
// Cette méthode a l'avantage de décomposer les étapes afin de les récupérer pour d'autres traitements au besoin
// Les étapes sont assez parlantes
//-------------------------------------------------------------------------------------------------------------------------

let fnConvertLUP = (pTable as table, pCase as text) as table =>
    let
        TableColumnNames =
            Table.ColumnNames(pTable),

        // Liste des colonnes à traiter
        ConvertListToTable =
            Table.FromList(
                TableColumnNames,
                Splitter.SplitByNothing(),
                null,
                null,
                ExtraValues.Error),

        // Fonction interne pour la gestion de la casse
        FunctionCase= (pElem as text) =>
            if Text.Upper(pCase) = "U" then
                Text.Upper(pElem)
                else if Text.Upper(pCase) = "L" then
                        Text.Lower(pElem)
                        else if Text.Upper(pCase) = "P" then
                                Text.Proper(pElem)
                                else null,
        
        // Ajout d'une colonne temporaire on en profite pour supprimer les espaces inutiles
        AddColumn =
            Table.AddColumn(
                ConvertListToTable,
                "__Case__",
                each Text.Trim(FunctionCase([Column1])
                    )
            ),
        
        // Création d'une liste temporaire pour le renommage
        ConvertToList =
            Table.AddColumn(
                AddColumn,
                "__ToList__",
                each Record.ToList(_)),
        
        // Passage en colonne dans la table résultat
        ConvertColumns =
            ConvertToList[__ToList__],
        
        // Renommage des colonnes
        RenameColumns =
            Table.RenameColumns(
                pTable,
                ConvertColumns)
    in
        RenameColumns
in
 fnConvertLUP

Par cette variante on peut choisir le type de résultat à obtenir

//-------------------------------------------------------------------------------------------------------------------------
// Renommage des colonnes d'une table avec remplacement de caractères et modification de la casse
// Cette méthode a l'avantage de décomposer les étapes afin de les récupérer pour d'autres traitements au besoin
// Les étapes sont assez parlantes
//
// Là cette version on choisit le type de résultat en sortie dans la clause in
// - soit la table renommée
// - soit la table + la liste des colonnes
// - soit la liste des colonnes renommées de manière isolée
//-------------------------------------------------------------------------------------------------------------------------


let fnConvertLUP = (pTable as table, pCase as text, pOutput) as any =>
    let
        TableColumnNames =
            Table.ColumnNames(pTable),

        ConvertListToTable =
            Table.FromList(
                TableColumnNames,
                Splitter.SplitByNothing(),
                null,
                null,
                ExtraValues.Error),

        FunctionCase= (pElem as text) =>
            if Text.Upper(pCase) = "U" then
                Text.Upper(pElem)
                else if Text.Upper(pCase) = "L" then
                        Text.Lower(pElem)
                        else if Text.Upper(pCase) = "P" then
                                Text.Proper(pElem)
                                else null,
        
        AddColumn =
            Table.AddColumn(
                ConvertListToTable,
                "__Case__",
                each Text.Trim(FunctionCase([Column1])
                    )
            ),
        
        ConvertToList =
            Table.AddColumn(
                AddColumn,
                "__ToList__",
                each Record.ToList(_)),
        
        ConvertColumns =
            ConvertToList[__ToList__],
            
        RenameColumns =
            Table.RenameColumns(
                pTable,
                ConvertColumns)

    //-----------------------------------------------------
    // Sélection du résultat souhaité
    //-----------------------------------------------------
    in
        if Text.Upper(pOutput)="T" then
            RenameColumns
            else
                if Text.Upper(pOutput)="TL" then
                   ConvertToList
                   else if Text.Upper(pOutput)="L" then
                           ConvertColumns
                           else null
in
 fnConvertLUP

Méthode 4 - ajout de préfixe ou de suffixe

//-------------------------------------------------------------------------------------------------------
// Fonction permettant de renommer des colonnes en suffixe ou préfixe
//-------------------------------------------------------------------------------------------------------
let fnConvertTableColumnsSP = (pSource as any,pSuffixPRefix as text, pDelim as text, pStr as text) as table =>   
    let
        pTable=if pSource is text then
                Excel.CurrentWorkbook(){[Name=pSource]}[Content]
                else pSource,
                        
        CaseSource = if Text.Upper(pSuffixPRefix) = "PREFIX" then
                        Table.TransformColumnNames(pTable,  each pStr & pDelim & _ )
                        else if Text.Upper(pSuffixPRefix) = "SUFFIX" then
                                Table.TransformColumnNames(pTable,  each _ & pDelim & pStr )
                                else pTable
    in
        CaseSource
in
    fnConvertTableColumnsSP

Utilisation des fonctions

let
    Source = fnRenameAll1 (TB_Source,TB_RENOM)
in
    Source

let
    Source = fnRenameAll2 (TB_Source,TB_RENOM)
in
    Source

let
    Source = fnConvertColumnLUP1 (TB_Source," ", "_","U")
in
    Source

let
    Source = fnConvertColumnLUP2 (TB_Source," ", "$$","U")
in
    Source

let
    Source=fnConvertColumnLUP4(TB_Source,"P")
in
    Source

let
    Source=fnConvertColumnLUP5(TB_Source,"U","T")
in
    Source

let
    Source=fnConvertColumnLUP5(TB_Source,"U","l")
in
    Source

let
    Source=Table.RenameColumns(TB_Source,RenameAll_22_List)
in
    Source

let
    Source=TB_Source,
    LstColumnsConvert=fnConvertColumnLUP5(TB_Source,"U","l"),
    Convert=Table.RenameColumns(Source,LstColumnsConvert)
in
    Convert

let
    Source=TB_Source,
    LstColumnsConvert=fnConvertColumnLUP5(TB_Source,"U","l"),
    //fnConvertTableColumnsSP = (pSource as any,pSuffixPRefix as text, pDelim as text, pStr as text) 
    Convert=fnConvertColumnSP1(Source,"PREFIX","-","PREF")
in
    Convert


let
    Source=TB_Source,
    LstColumnsConvert=fnConvertColumnLUP5(TB_Source,"U","l"),
    //fnConvertTableColumnsSP = (pSource as any,pSuffixPRefix as text, pDelim as text, pStr as text) 
    Convert=fnConvertColumnSP1(Source,"SUFFIX","-","SUF")
in
    Convert

 

[combory]

Bonjour,
C'est tout simplement génial et c'est exactement ma problématique du moment.
J'ai cependant une question. Comment gérer les erreurs lorsque dans la table TB_RENOM il y a 2 fois la même valeur dans la colonne NEW ?

Merci pour ce super boulot

 

[oguruma]

Bonjour,
C'est tout simplement génial et c'est exactement ma problématique du moment.
J'ai cependant une question. Comment gérer les erreurs lorsque dans la table TB_RENOM il y a 2 fois la même valeur dans la colonne NEW ?

Merci pour ce super boulot

Hello, merci pour ce retour.
Un post va suivre bientôt toujours sur les combine et cette fois j'ai un peu blinder le truc....
En fait il faut découper en fonctions et tester le retour et y mettre du try otherwise qui renvoie null par exemple ou une table vide avec le constructeur #table ({},{})
et derrière cela devrait aussi suivre un classeur de synthèse qui regroupera toutes les fonctions présentées dans les divers post sous forme de modules mettant en oeuvre la clause section avec un shared
en fait on peut faire comme en VB.... faire un module dans lequel il y a des fonctions que l'on appelle par NOM_DU_MODULE.Fonction ==> NOM_DU_MODULE c'est en fait une requête construite comme une fonction... bon j'en ai peut-être perdu là

 

[oguruma]

Un post aussi sur l'équivalent du Select Case (VBA) devrait suivre bien et ce dernier viendra enrichir cette compilation. Par rapport à ce qui existe déjà sur la toile à ce propos ma version permet soit de traiter des passage de valeur en liste soit en texte et de retourner soit selon la valeur demandée un type text, un type date, un type list, un type table grâces aux merveilles de Expression.Evaluate (rappel c'est un peu le INDIRECT sous Excel)