POWERQUERY :: Combinaison de tableaux structurés Excel pouvant être issus de répertoires différents avec des noms de tableaux structurés différents

[oguruma]

Bonjour le Forum,
Excel/PowerQuery permet de fusionner des classeurs se trouvant dans un dossier (voir sous-dossiers dépendants du dossier sélectionné).
Il suffit de laisser faire les assistants via ceci

Bien que très pratique pour les non initiés ou pour les débutants sous PowerQuery il est assez difficile de piloter ce que l'on souhaite réellement combiner. J'ai déjà à ce propos publié plusieurs utilitaires PowerQuery personnalisables
ICI puis ICI et ICI et enfin ICI ou optionnellement ICI

L'objet de ce post est de démontrer aussi que l'on peut combiner plusieurs fichiers se trouvant dans des répertoires différents comportant des noms de tableaux structurés différents. Attention les tableaux doivent avoir la même structure. Cela entend donc : l'ordre des colonnes doit être identique pour tous, les noms de colonnes doivent être aussi identiques.

Cela est donc entièrement réalisable via ce petit exemple.

On dresse dans un 1er temps une table de paramétrage comme ci-dessous :

TAG_PREMIERE_COLONNE permet de préciser si colonne TAG devra être placée en 1ère position.

Le paramètre TABLE_LIST_COMBINE doit contenir le nom de la table identifiant les fichiers Excel à fusionner comme celle-ci :

Les noms des colonnes ne sont pas imposés. C'est selon votre choix car dans PowerQuery nous allons utiliser leurs positions dans le tableau. Il faut en revanche respecter cet ordre.

TABLE_COMBINE : représente le nom du tableau structuré à importer
ENSEIGNE : Cette colonne a une double fonction. Elle permet d'identifier le tableau qui sera fusionné par l'ajout d'une colonne comportant le TAG associé. Le nom de la colonne TAG sera le nom de la colonne dans ce tableau. Dans notre exemple la colonne sera nommée ENSEIGNE.

Une fonction dédiée dans la requête sera chargée de parcourir ce tableau pour fusionner les fichiers indiqués.
Les lignes vides sont ignorées.
La fonction vérifie la présence du tableau. Si celui-ci n'existe pas comme TB_RH_INCONNUE il n'y aura aucune combinaison et donc aucun plantage.
Idem si le nom du tableau n'est pas renseigné il n'y aura aucun traitement et donc aucun plantage.
On remarque pour le fichier BDDRH_12 que le tableau structuré est lui nommé TB_SALARIES et ça fonctionne. Bien entendu il devra être de même structure que TABLE_RH du 1er fichier.
Les lignes avec un fichier inconnu sont ignorées (test préalable de l'existence du fichier avant combinaison)

Dans l'exemple le dossier est identique. Mais étant donné que l'on passe en paramètre le nom complet du fichier et que celui-ci est exploité directement cela fonctionnera pour des répertoires différents. A vous d'installer vos fichiers dans différents répertoires au moment de vos tests.

Comme d'habitude j'invite les débutants en langage M/PowerQuery à bien examiner le code qui comporte de nombreuses astuces de développements en langage M.

Le code est très largement documenté.

let

    //******************************************************************************
    //                      FONCTIONS SECTION
    //******************************************************************************

    //******************************************************************************
    // Combinaison des fichiers
    //******************************************************************************
    COMBINE = () as any =>
            let
                ListCombinedFiles=List.Accumulate(
                    TBL_COMBINE_TO_RECORDS, // Liste des fichiers Excel à traiter
                    #table({},{}),          // On initialise l'accumulateur avec une table vide pour démarrer la fusion avec le 1er fichier Excel
                    (state,current)=>       // current = fichier Excel en cours de traitement
                        let
                            CurrentFile=Record.Field(current,LIST_COLUMNS_TBL_COMBINE{0}),  // Nom du fichier à traiter
                            IsFileExists=fnFileExists(CurrentFile),                         // On vérifie l'existance du fichier
                            CurrentTag=Record.Field(current,LIST_COLUMNS_TBL_COMBINE{1}),   // Nom de l'étiquette permettant de différencier les fichiers
                            CurrentItemTmp=Record.Field(current,LIST_COLUMNS_TBL_COMBINE{2}),  // Nom du tableau qui sera fusionné
                            CurrentItem=if CurrentItemTmp is null then "<NULL___ITEM_>" else CurrentItemTmp, // Mon met volontairement un item inconnu pour ne pas planter ainsi on passera au suivant
                            isExistsIem = fnIsExistsTable(CurrentFile,CurrentItem),         // On vérifie si le tableau existe dans le fichier en cours de traitement                  
                            TableCombine=if IsFileExists then if isExistsIem                // si toutes les connditions sont réunnies on fusionne les données
                                            then
                                                let
                                                    // On récupère le fichier Excel comportant le tableau à fusionner
                                                    wbExcelTmp=try Excel.Workbook(File.Contents(CurrentFile), null, true){[Item=CurrentItem,Kind="Table"]}[Data] otherwise state,
                                                    // 1ère ligne = titre des colonnes à voir les type des fichiers
                                                    wbExcel=if STR_PROMOTE ="OUI" then Table.PromoteHeaders(wbExcelTmp, [PromoteAllScalars=true]) else wbExcelTmp,
                                                    // on ajoute la colonne étiquette qui différencie les fichiers importés
                                                    wbAddCol=Table.AddColumn(wbExcel,LIST_COLUMNS_TBL_COMBINE{1}, each CurrentTag),
                                                    // on procède à la fusion
                                                    wbCombine=Table.Combine({state, wbAddCol})
                                                in
                                                    wbCombine // Résultat de la combinaison
                                            else state // Si Item n'existe pas on renvoie l'état de l'accumulateur qui permet de passer au fichier suivant sans plantage
                                         else state    // Si le fichier n'existe pas on renvoie l'état de l'accumulateur qui permet de passer au fichier suivant sans planatge
                        in
                            TableCombine               // Table finale comportant tous les tableaux fusionnés
                )
            in
                ListCombinedFiles,                     // La fonction renvoie le tableau fusionné

    //******************************************************************************
    // Déplacement de l'étiquette à la 1ère colonne
    //******************************************************************************
    MOVE_TAG_COLUM = () as any =>
            let
                LstColumns=Table.ColumnNames(TBL_DATA_COMBINE),
                NbColumns=List.Count(LstColumns) - 1,
                L1={LIST_COLUMNS_TBL_COMBINE{1}},
                L2=List.FirstN(LstColumns,NbColumns),
                LstCombine=List.Combine({L1,L2}),
                TblReorder=Table.ReorderColumns(TBL_DATA_COMBINE,LstCombine)
            in
                TblReorder,
               
    //******************************************************************************
    //                      PRINCIPALE SECTION
    //******************************************************************************

    //******************************************************************************
    // Identification des paramètres
    //******************************************************************************
    STR_PROMOTE=Text.Upper(fnGetParameter("TB_PARAMS", "PROMOTE")),
    STR_TBL_COMBINE=fnGetParameter("TB_PARAMS", "TABLE_LIST_COMBINE"),
    STR_TAG_PREM=fnGetParameter("TB_PARAMS", "TAG_PREMIERE_COLONNE"),  

    //******************************************************************************
    // On prépare les éléments pour la fusion
    //******************************************************************************
    TBL_COMBINE = Excel.CurrentWorkbook(){[Name=STR_TBL_COMBINE]}[Content],
    LIST_COLUMNS_TBL_COMBINE=Table.ColumnNames(TBL_COMBINE),
    STR_NOM_COLONNE_COMBINE=LIST_COLUMNS_TBL_COMBINE{0},
    TBL_COMBINE_TO_RECORDS=Table.ToRecords(TBL_COMBINE),

    //******************************************************************************
    // Fusion des fichiers
    //******************************************************************************
    TBL_DATA_COMBINE=COMBINE(),

    //******************************************************************************
    // Déplacement de la colonne étiquette
    //******************************************************************************
    TBL_DATA_FINALE=if STR_TAG_PREM = "OUI" or STR_TAG_PREM is null then MOVE_TAG_COLUM() else TBL_DATA_COMBINE

in
    TBL_DATA_FINALE

Les fonctions annexes

Tester l'existence d'une table

let fnIsExistsTable =
    (
        pExceFile   as text,
        pItem       as text
    ) as any =>
        let
            Source = Excel.Workbook(File.Contents(pExceFile), null, true),
            TBL_FILTER = Table.SelectRows(Source, each ([Kind] = "Table")),
            TBL_Table = Table.RemoveColumns(TBL_FILTER,{"Data", "Kind", "Hidden"})
        in          
            let
                StrParam="each ([Name]) = " & """" & pItem & """",
                EvalParam=Expression.Evaluate(StrParam,[TBL_Table=TBL_Table]),
                TBL_ITEM=Table.SelectRows(TBL_Table,EvalParam),
                IsExists=if Table.IsEmpty(TBL_ITEM)=true then false else true
            in
                IsExists
in
    fnIsExistsTable

Tester l'existence d'un fichier

let fnFileExists = (
                    pFilePathFullName as any
        ) as logical =>
        let
            FilePathNameString = pFilePathFullName,

            //=========================================================================================
            // Longueur totale du nom de fichier avec son chemin
            //=========================================================================================
            Length = Text.Length(FilePathNameString),

            //=========================================================================================
            // On calcule la position du dernier slash pour identifer le fichier
            //=========================================================================================
            PositionLastSlash = Text.PositionOf(FilePathNameString,"\",Occurrence.Last),

            //=========================================================================================
            // Chemin du fichier
            //=========================================================================================
            FolderPathName = Text.Start(FilePathNameString,PositionLastSlash + 1),

            //=========================================================================================
            // Nom du fichier
            //=========================================================================================
            FileName = Text.End(FilePathNameString,Length - PositionLastSlash - 1),

            //=========================================================================================
            // V�rification de l'existance du fichier
            //=========================================================================================
            RecordAttributes = try Folder.Contents(FolderPathName){[Name=FileName]}[Attributes] otherwise false,
            FileExists=if RecordAttributes is record then true else false,
            isExists = if FileExists then true else false
        in
            isExists
in
    fnFileExists