POWERQUERY :: Importation et optimisation des étapes - Table.TransformColumns - Table.TransformRows - Record.AddField - Record.TransformFields

oguruma · 24 Février 2025

Bonjour le Forum,
A travers ce mini tuto comment importer un fichier Excel en optimisant les étapes de transformations à la volée directement dans les colonnes sans créer de colonnes temporaires additionnelles.

Exemple : Importer ce fichier et procéder à des traitements pendant l'importation

Exemple 1

Nous allons importer ce fichier et au passage transformer la colonne MLE en préfixant par un zéro de manière à avoir 6 caractères puis les séparer par groupe de deux avec le séparateur "-" afin d'avoir ceci

Nous allons procéder à l'importation en passant par les assistants. Cela nous donne la requête suivante

Nous sommes donc passés par une colonne intermédiaire temporaire afin de pouvoir transformer la colonne MLE.
Puis il a été nécessaire de supprimer la colonne initiale et de renommer la colonne temporaire par le nom de la colonne initiale.

PowerQuery:

let

    //*****************************************************************************************************
    // On récupère les paramètres permettant d'importer le fichier
    //*****************************************************************************************************
    STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
    STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
    STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),
   
    Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
    TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
    TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),

    //*****************************************************************************************************
    // LES OPERATIONS CI-DESSOUS SERONT REMPLACEES PAR UNE SEULE ETAPE PAR LA FONCTION
    //                      Table.TransformColumns
    //*****************************************************************************************************
    // Qui consiste à :
    //  -Transformation directement dans la colonne
    //  -Ca évite de passer par une colonne temporaire afin de formater le MLE comme souhaité
    //  -Ca évite aussi de supprimer l'ancienne colonne et de renommer la nouvelle par le nom de l'ancienne
    //  -sachant qu'il aurait fallu replacer la nouvelle colonne en tête de la table
    //  -on gagne ainsi 3 étapes ce qui peut être un gain en cas de forte volumétrie
    //*****************************************************************************************************

    #"Personnalisée ajoutée" = Table.AddColumn(TBL_PROMOTE, "MLE2", each let
            s1=Text.PadStart([MLE],6,"0"),
            s2=Text.Start(s1,2),  
            s3=Text.Middle(s1,3,2),
            s4=Text.End(s1,2),
            S=s2 & "-" & s3 & "-" & s4
        in
            S),
           
    #"Colonnes supprimées" = Table.RemoveColumns(#"Personnalisée ajoutée",{"MLE"}),
    #"Colonnes renommées" = Table.RenameColumns(#"Colonnes supprimées",{{"MLE2", "MLE"}}),
    #"Colonnes permutées" = Table.ReorderColumns(#"Colonnes renommées",{"MLE", "NOM", "PRENOM", "SEXE", "DEPT", "AGE", "CONTRAT", "DATE ENTREE", "SERVICE", "SALAIRE"})
in
    #"Colonnes permutées"

Ces 3 étapes peuvent être regroupées en une seule avec la fonction Table.TransformColumns

PowerQuery:

let

    //*****************************************************************************************************
    // On récupère les paramètres permettant d'importer le fichier
    //*****************************************************************************************************
    STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
    STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
    STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),
   
    Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
    TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
    TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),

    //*****************************************************************************************************
    // Transformation directement dans la colonne
    // Ca évite de passer par une colonne temporaire afin de formater le MLE comme souhaité
    // Ca évite aussi de supprimer l'ancienne colonne et de renommer la nouvelle par le nom de l'ancienne
    // sachant qu'il aurait fallu replacer la nouvelle colonne en tête de la table
    // on gagne ainsi 3 étapes ce qui peut être un gain en cas de forte volumétrie
    //*****************************************************************************************************
    TBL_TRANSFORM_COLUMNS=Table.TransformColumns(
        TBL_PROMOTE,
        {
            //***********************************************************************************************
            // On fixe le Matricule, on complète par des zéros à droite pour avoir le même nbr de caractères
            // ensuite pour l'exemple on le décompose en 3 parties
            //***********************************************************************************************
            {"MLE",            
             each
                let
                    strPad=Text.PadStart(_,6,"0"),
                    strS1=Text.Start(strPad,2),
                    strS2=Text.Middle(strPad,3,2),
                    strS3=Text.End(strPad,2),
                    strMleFinal="ID-" & strS1 & "-" & strS2 & "-" & strS3
                in
                    strMleFinal,
             type text}
        }

    )
in
    TBL_TRANSFORM_COLUMNS

Exemple 2

Ci-dessous un exemple avancé de transformation sur plusieurs colonnes

PowerQuery:

let
    //*****************************************************************************************************
    // On récupère les paramètres permettant d'importer le fichier
    //*****************************************************************************************************
    STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
    STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
    STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),
   
    Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
    TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
    TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),

    //*****************************************************************************************************
    // Transformation directement dans les colonnes
    // Ca évite de passer par une colonne temporaire afin de formater le MLE comme souhaité
    // Ca évite aussi de supprimer l'ancienne colonne et de renommer la nouvelle par le nom de l'ancienne
    // sachant qu'il aurait fallu replacer la nouvelle colonne en tête de la table
    // on gagne ainsi 3 étapes ce qui peut être un gain en cas de forte volumétrie
    // Toutes ces opérations auraient nécessité des colonnes intermédiaires à renommer après suppression
    // des colonnes d'origine
    //*****************************************************************************************************

    TBL_TRANSFORM_COLUMNS=Table.TransformColumns(
        TBL_PROMOTE,
        {
            {"MLE",            
             (xMLE)=>
                let
                    strPad=Text.PadStart(xMLE,6,"0"),
                    strS1=Text.Start(strPad,2),
                    strS2=Text.Middle(strPad,3,2),
                    strS3=Text.End(strPad,2),
                    strMleFinal= strS1 & strS2 & strS3
                in
                    strMleFinal,
             type text},

            //*********************************************
            // Conversion de la date en AAAAMMJJ
            //*********************************************
             {"DATE ENTREE",
                (xDATE)=>
                    let
                        strDay=Text.PadStart(Number.ToText(Date.Day(xDATE)),2,"0"),
                        strYear=Number.ToText(Date.Year(xDATE)),
                        strMonth=Text.PadStart(Number.ToText(Date.Month(xDATE)),2,"0"),
                        strDate=strYear & strMonth & strDay
                    in
                        strDate,
             type text},

            //*********************************************
            // On préfixe le type de contrat
            //*********************************************
             {"CONTRAT",
                 (xCONTRAT)=>
                    let
                        code=if xCONTRAT="CDI" then "01"
                             else if xCONTRAT="CDD" then "02"
                                  else "99"
                    in
                        code & "-" & xCONTRAT
             }
        }
    )
in
    TBL_TRANSFORM_COLUMNS

Nous intervenons ici sur 3 colonnes.

Exemple 3

Ces transformations multiples en direct dans les colonnes sont aussi possibles avec la fonction Table.TransformRows
Celle-ci offre l'avantage de pouvoir récupérer les valeurs des autres colonnes étant donné que l'on travaille sur le RECORD ce qui n'est pas le cas de la fonction Table.TransformColumns qui elle travaille uniquement sur la colonne. Le RECORD n'est donc pas accessible dans totalité.

Nous obtenons ceci

On remarque les multiples transformations dont certaines assez complexes.
Pour l'exemple la colonne MLE est combinée avec la date sous forme AAAAMMJJ.
Les colonnes NOM et PRENOM ont été renommées.
Les colonnes CONTRAT et SERVICE converties en ProperCase
Création des colonnes spécifiques DATE_AAAAMMJJ, PCT_AUGMENT, MONTANT_AUGMENT, NOUVEAU_SALAIRE

Tous ces traitements auraient réalisables via les assistants au détriment d'opérations multiples qui auraient ralenti les traitement en cas de forte volumétrie.

PowerQuery:

let
    //*****************************************************************************************************
    // On récupère les paramètres permettant d'importer le fichier
    //*****************************************************************************************************
    STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
    STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
    STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),

    //*****************************************************************************************************
    // On récupère les taux d'augmentations
    //*****************************************************************************************************
    pct_Cpt=fnGetParameter("TB_PARAMS","COMPTABILITE","PARAM", "VALEUR"),
    pct_Comm=fnGetParameter("TB_PARAMS","COMMERCIAL","PARAM", "VALEUR"),
    pct_Ach=fnGetParameter("TB_PARAMS","ACHATS","PARAM", "VALEUR"),
    pct_Paie=fnGetParameter("TB_PARAMS","PAIE","PARAM", "VALEUR"),
    pct_Info=fnGetParameter("TB_PARAMS","INFORMATIQUE","PARAM", "VALEUR"),  
   
    Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
    TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
    TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),

    //*****************************************************************************************************
    // Transformation directement dans les colonnes
    // Ca évite de passer par une colonne temporaire afin de formater le MLE comme souhaité
    // Ca évite aussi de supprimer l'ancienne colonne et de renommer la nouvelle par le nom de l'ancienne
    // sachant qu'il aurait fallu replacer la nouvelle colonne en tête de la table
    // on gagne ainsi 3 étapes ce qui peut être un gain en cas de forte volumétrie
    //*****************************************************************************************************

    TBL_TRANSFORM_COLUMNS=Table.TransformColumns(
        TBL_PROMOTE,
        {
            {"MLE",            
             (xMLE)=>
                let
                    strPad=Text.PadStart(xMLE,6,"0"),
                    strS1=Text.Start(strPad,2),
                    strS2=Text.Middle(strPad,3,2),
                    strS3=Text.End(strPad,2),
                    strMleFinal= strS1 & strS2 & strS3
                in
                    strMleFinal,
             type text}
        }
    ),

    //*****************************************************************************************************
    // Transformation directement dans les enregistrements RECORDS
    // Ca évite de passer par une colonne temporaire afin de formater le MLE comme souhaité
    // Ca évite aussi de supprimer l'ancienne colonne et de renommer la nouvelle par le nom de l'ancienne
    // sachant qu'il aurait fallu replacer la nouvelle colonne en tête de la table
    // on gagne ainsi 3 étapes ce qui peut être un gain en cas de forte volumétrie
    //*****************************************************************************************************
    // Le fait de passer par Table.TransformRows permet d'utiliser tous les champs du RECORD pour
    // transformmer le champ(colonne) d'un RECORD
    //*****************************************************************************************************

    TBL_TRANSFORM_ROWS=Table.TransformRows(
            //*********************************************************************************************
            // De la même manière que Table.TransformColumns on peut adresse directement un champ/colonne
            // pour y faire des transformation en direct sans devoir passer par une colonne intermédiaire
            // C'est aussi l'un des moyens pour ajouter des nouvelles colonnes pendant les opérations
            // de transformation
            //*********************************************************************************************
            TBL_TRANSFORM_COLUMNS,
               each let
                    //************************************************************************************
                    // Construction de la nouvelle colonne DATE_AAAAMMJJ - elle sera ajoutée au RECORD
                    //************************************************************************************
                    DATE_AAAAMMJJ=
                        let
                            strDay=Text.PadStart(Number.ToText(Date.Day([DATE ENTREE])),2,"0"),
                            strYear=Number.ToText(Date.Year([DATE ENTREE])),
                            strMonth=Text.PadStart(Number.ToText(Date.Month([DATE ENTREE])),2,"0"),
                            strDate=strYear & strMonth & strDay
                        in
                            strDate,
                    //************************************************************************************
                    // Construction de la nouvelle colonne CODE_CONTRAT - elle sera ajoutée au RECORD
                    //************************************************************************************                            
                    CODE_CONTRAT=
                        let
                            code=if [CONTRAT]="CDI" then "01"
                                else if [CONTRAT]="CDD" then "02"
                                    else "99"
                        in
                            code,
                    //************************************************************************************
                    // Construction de la nouvelle colonne TAUX_AUGMENT - elle sera ajoutée au RECORD
                    //************************************************************************************
                    TAUX_AUGMENT=
                        let
                            taux=if [SERVICE]="COMPTABILITE" then pct_Cpt
                                 else if [SERVICE]="COMMERCIAL" then pct_Comm
                                      else if [SERVICE]="ACHATS" then pct_Ach
                                           else if [SERVICE]="PAIE" then pct_Paie
                                                else if [SERVICE]="INFORMATIQUE" then pct_Info
                                                     else 0
                        in
                            taux,
                    //************************************************************************************
                    // Construction de la nouvelle colonne AUGMENT - elle sera ajoutée au RECORD
                    //************************************************************************************
                    AUGMENT=
                        let
                            augment=Number.RoundUp([SALAIRE] * TAUX_AUGMENT,2)
                        in
                            augment,
                    //************************************************************************************
                    // Construction de la nouvelle colonne NEW_SALAIRE - elle sera ajoutée au RECORD
                    //************************************************************************************
                    NEW_SALAIRE=
                        let
                            newSal=[SALAIRE] + AUGMENT
                        in
                            newSal,
                    //************************************************************************************
                    // On ajoute à l'enregistrement les nouvelles colonnes calulées ci-dessus
                    //************************************************************************************
                    FLD_DATE_AAAAMMJJ=Record.AddField(_, "DATE_AAAAMMJJ",DATE_AAAAMMJJ),
                    FLD_CODE_CONTRAT=Record.AddField(FLD_DATE_AAAAMMJJ, "CODE_CONTRAT",CODE_CONTRAT),
                    FLD_TAUX=Record.AddField(FLD_DATE_AAAAMMJJ, "PCT_AUGMENT",TAUX_AUGMENT),
                    FLD_AUGMENT=Record.AddField(FLD_TAUX, "MONTANT_AUGMENT",AUGMENT),
                    FLD_SALAIRE_2=Record.AddField(FLD_AUGMENT, "NOUVEAU SALAIRE",NEW_SALAIRE),

                    //************************************************************************************
                    // On profite de cette opération de transformation pour recommer des colonnes
                    //************************************************************************************
                    FLD_RENAME=Record.RenameFields(FLD_SALAIRE_2,{
                                                        {"NOM","NOM_SALARIE"},
                                                        {"PRENOM","PREN_SALARIE"}
                                                      }),
                                                     
                    //************************************************************************************
                    // Fonction permettant d'intervenir directement sur le contenu d'un champ (colonne)
                    // On procède à de nouvelles transformation en direct dans les colonnes
                    //************************************************************************************
                    FLD_TRANSFORM=Record.TransformFields(

                                FLD_RENAME,
                                //*************************************************************************
                                // Transformation du type
                                //*************************************************************************
                                {
                                    {"CONTRAT", Text.Proper},
                                    {"NOM_SALARIE", Text.Upper},
                                    {"PREN_SALARIE", Text.Proper},
                                    {"SERVICE", Text.Proper},

                                    //*********************************************************************
                                    // On peut effectuer des traitements conditionnels en direct
                                    // dans une colonne - (xSEXE)=> équivaut à each avec la variable
                                    // - qui représente la colonne en cours de traitement
                                    //*********************************************************************
                                    {"SEXE", (xSEXE)=>  // seconde forme du each et _
                                        if xSEXE = "M" then "Masculin"
                                        else if xSEXE ="F" then "Féminin"
                                            else "Inconnu"
                                    },

                                    //*********************************************************************
                                    // Intervention directement dans la colonne MLE pour un formatage
                                    // avec la date AAAAMMJJ
                                    //*********************************************************************

                                    //************************************************************************************************
                                    // Les deux syntaxes fonctionnent parfaitement
                                    // FLD_RENAME représente le RECORD en cours de lecture
                                    // La second a l'avantage de pouvoir adresser un nom de colonne dynamiquement via une variable
                                    //************************************************************************************************

                                    // {"MLE", each _ & "-" & FLD_RENAME[DATE_AAAAMMJJ]}
                                    {"MLE", each _ & "-" & Record.Field(FLD_RENAME,"DATE_AAAAMMJJ")}
                                }
                            )
                in
                    //***************************************************************************************
                    // A l'issue des transformation Rows on obtient une liste de RECORDS
                    //***************************************************************************************
                    FLD_TRANSFORM
    ),

    //***************************************************************************************
    // Il faut transformer cette liste de RECORDS en table pour obtenir le résultat final
    //***************************************************************************************
    TBL=Table.FromRecords(TBL_TRANSFORM_ROWS)
   

in
    TBL

La requête est certes un peu complexe car elle demande déjà une bonne maîtrise du langage M. C'est cependant un bon exemple pour vos futurs traitements. Il suffit de vous en inspirer comme modèle de syntaxe à employer.

Autre exemple dans le même style

PowerQuery:

let
    //*****************************************************************************************************
    // On récupère les paramètres permettant d'importer le fichier
    //*****************************************************************************************************  
    STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
    STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
    STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),
   
    Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
    TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
    TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),

    //*****************************************************************************************************
    // Transformation directement dans les enregistrements RECORDS
    // Ca évite de passer par une colonne temporaire afin de formater le MLE comme souhaité
    // Ca évite aussi de supprimer l'ancienne colonne et de renommer la nouvelle par le nom de l'ancienne
    // sachant qu'il aurait fallu replacer la nouvelle colonne en tête de la table
    // on gagne ainsi 3 étapes ce qui peut être un gain en cas de forte volumétrie
    //*****************************************************************************************************
    // Le fait de passer par Table.TransformRows permet d'utiliser tous les champs du RECORD pour
    // transformmer le champ(colonne) d'un RECORD
    //*****************************************************************************************************

    TBL_TRANSFORM_ROWS=Table.TransformRows(
            TBL_PROMOTE,
               each let
             
                    DATE_AAAAMMJJ=
                        let
                            strDay=Text.PadStart(Number.ToText(Date.Day([DATE ENTREE])),2,"0"),
                            strYear=Number.ToText(Date.Year([DATE ENTREE])),
                            strMonth=Text.PadStart(Number.ToText(Date.Month([DATE ENTREE])),2,"0"),
                            strDate=strYear & strMonth & strDay
                        in
                            strDate,
                           
                    CODE_CONTRAT=
                        let
                            code=if [CONTRAT]="CDI" then "01"
                                else if [CONTRAT]="CDD" then "02"
                                    else "99"
                        in
                            code,
                           
                    FLD_DATE_AAAAMMJJ=Record.AddField(_, "DATE_AAAAMMJJ",DATE_AAAAMMJJ),
                    FLD_CODE_CONTRAT=Record.AddField(FLD_DATE_AAAAMMJJ, "CODE_CONTRAT",CODE_CONTRAT),

                    FLD_RENAME=Record.RenameFields(FLD_CODE_CONTRAT,{
                                                        {"NOM","NOM_SALARIE"},
                                                        {"PRENOM","PREN_SALARIE"}
                                                      }),

                    //***********************************************************************************
                    // Fonction permettant d'intervenir directement sur le contenu d'un champ (colonne)
                    //***********************************************************************************
                    FLD_TRANSFORM=Record.TransformFields(                      
                                FLD_RENAME,
                                {
                                    //********************************************************
                                    // On intervention directement sur le type de la colonne
                                    //********************************************************
                                    {"CONTRAT", Text.Proper},
                                    {"NOM_SALARIE", Text.Upper},
                                    {"PREN_SALARIE", Text.Proper},
                                    {"SERVICE", Text.Proper},

                                    //************************************************************************
                                    // Traitement conditionnel sur la colonne SEXE directement sur la colonne
                                    //************************************************************************
                                    {"SEXE", (xSEXE)=>
                                        if xSEXE = "M" then "Masculin"
                                        else if xSEXE ="F" then "Féminin"
                                            else "Inconnu"
                                    },
                                    //********************************************************
                                    // Formtage spécifique de la colonne MLE
                                    //********************************************************
                                    {"MLE", (FLD_MLE)=>  // seconde forme du each suivi de _
                                        let
                                            strPad=Text.PadStart(FLD_MLE,6,"0"),
                                            strS1=Text.Start(strPad,2),
                                            strS2=Text.Middle(strPad,3,2),
                                            strS3=Text.End(strPad,2),
                                            strMleFinal= strS1 & strS2 & strS3,
                                            newMLE=strMleFinal & "-" & FLD_RENAME[DATE_AAAAMMJJ]
                                        in
                                            newMLE
                                    }
                                }
                            )
                in
                    FLD_TRANSFORM
    ),
    //***************************************************************************************
    // Il faut transformer cette liste de RECORDS en table pour obtenir le résultat final
    //***************************************************************************************
    TBL=Table.FromRecords(TBL_TRANSFORM_ROWS)  

in
    TBL

Exemple 4

Dans cet exemple on souhaite uniquement créer une table d'index à l'issue de l'importation pour obtenir ceci

On remarque à l'issue de l'importation on ne retient que deux colonnes que l'on a transformer pendant les traitements.

Méthode en passant par les assistants

PowerQuery:

let

    //*****************************************************************************************************
    // On récupère les paramètres permettant d'importer le fichier
    //*****************************************************************************************************
    STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
    STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
    STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),
   
    Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
    TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
    TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),
    #"Personnalisée ajoutée" = Table.AddColumn(TBL_PROMOTE, "MLE2", each let
            s1=Text.PadStart([MLE],6,"0"),
            s2=Text.Start(s1,2),  
            s3=Text.Middle(s1,3,2),
            s4=Text.End(s1,2),
            S=s2 & s3 & s4
        in
            S),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Personnalisée ajoutée",{"MLE"}),
    #"Colonnes renommées" = Table.RenameColumns(#"Colonnes supprimées",{{"MLE2", "MLE"}}),
    #"Colonnes permutées" = Table.ReorderColumns(#"Colonnes renommées",{"MLE", "NOM", "PRENOM", "SEXE", "DEPT", "AGE", "CONTRAT", "DATE ENTREE", "SERVICE", "SALAIRE"}),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Colonnes permutées", "Personnalisé", each [NOM] & ", " & [PRENOM]),
    #"Colonnes supprimées1" = Table.RemoveColumns(#"Personnalisée ajoutée1",{"NOM", "PRENOM", "SEXE", "DEPT", "AGE", "CONTRAT", "DATE ENTREE", "SERVICE", "SALAIRE"}),
    #"Colonnes renommées1" = Table.RenameColumns(#"Colonnes supprimées1",{{"Personnalisé", "SALARIE"}, {"MLE", "CLEF_INDEX"}})
in
    #"Colonnes renommées1"

Méthode optimisée

PowerQuery:

let
    //*****************************************************************************************************
    // On récupère les paramètres permettant d'importer le fichier
    //*****************************************************************************************************
    STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
    STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
    STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),
   
    Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
    TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
    TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),

    //*****************************************************************************************************
    // Transformation directement dans les colonnes
    // Ca évite de passer par une colonne temporaire afin de formater le MLE comme souhaité
    // Ca évite aussi de supprimer l'ancienne colonne et de renommer la nouvelle par le nom de l'ancienne
    // sachant qu'il aurait fallu replacer la nouvelle colonne en tête de la table
    // on gagne ainsi 3 étapes ce qui peut être un gain en cas de forte volumétrie
    // Toutes ces opérations auraient nécessité des colonnes intermédiaires à renommer après suppression
    // des colonnes d'origine
    //*****************************************************************************************************
    TBL_TRANSFORM_COLUMNS=Table.TransformColumns(
        TBL_PROMOTE,
        {
            {"MLE",            
             (xMLE)=>
                let
                    strPad=Text.PadStart(xMLE,6,"0"),
                    strS1=Text.Start(strPad,2),
                    strS2=Text.Middle(strPad,3,2),
                    strS3=Text.End(strPad,2),
                    strMleFinal=strS1 & strS2 & strS3
                in
                    strMleFinal,
             type text},

             {"DATE ENTREE",
                (xDATE)=>
                    let
                        strDay=Text.PadStart(Number.ToText(Date.Day(xDATE)),2,"0"),
                        strYear=Number.ToText(Date.Year(xDATE)),
                        strMonth=Text.PadStart(Number.ToText(Date.Month(xDATE)),2,"0"),
                        strDate=strYear & strMonth & strDay
                    in
                        strDate,
             type text},
           
             {"CONTRAT",
                 (xCONTRAT)=>
                    let
                        code=if xCONTRAT="CDI" then "01"
                             else if xCONTRAT="CDD" then "02"
                                  else "99"
                    in
                        code
             }
        }
    ),

    //**********************************************************************************************************
    // Par ce procédé on ne retient que les colonnes que l'on souhaite conserver
    // à l'issue des transformations ci-dessus
    // Ca éviter des étapes de suppressions de colonnes
    //**********************************************************************************************************
    TBL_TRANSFORM_ROWS=Table.TransformRows(TBL_TRANSFORM_COLUMNS,
        each [
                //**********************************************************************************************
                // Création des colonnes CLEF_INDEX et SALARIE en créant directement un RECORD via le each
                //**********************************************************************************************
                CLEF_INDEX = [MLE] & "-" &  [DATE ENTREE] & "-" & [CONTRAT],
                SALARIE=[NOM] & ", " & [PRENOM]
            ]),

    //***************************************************************************************
    // Il faut transformer cette liste de RECORDS en table pour obtenir le résultat final
    //***************************************************************************************
    TBL=Table.FromRecords(TBL_TRANSFORM_ROWS)
   
in
    TBL

POWERQUERY :: Importation et optimisation des étapes - Table.TransformColumns - Table.TransformRows - Record.AddField - Record.TransformFields

oguruma

XLDnaute Occasionnel

Pièces jointes

Discussions similaires

POWERQUERY :: Importation et optimisation des étapes - Table.TransformColumns - Table.TransformRows - Record.AddField - Record.TransformFields

oguruma

XLDnaute Occasionnel

Pièces jointes

Discussions similaires

Privacy & Transparency

Privacy & Transparency