POWERQUERY :: Combinaison de tableaux structurés Excel issus de dossiers différents permettant de fusionner soit des Table soit des Sheet -

[oguruma]

Bonjour Le Forum,
Comme évoqué dans le post

Combinaison de tableaux structurés Excel pouvant être issus de répertoires différents avec des noms de feuilles différentes​

voici la version permettant de choisir entre Table et Sheet.

Dans la table de paramétrage il suffisait de d'ajouter le paramètre Type comme ceci

Ce paramètre permettra d'aiguiller le type d'importation et le type d'objet ciblé dans le paramètre Kind.

Voici les exemples de paramétrages possibles :

Exemple de tables pour la liste des fichiers à parcourir

ENVIRONNEMENT POWERQUERY

RQ_IMPORT_DONNEES_01 et TB_FINALE ont été développés dans les versions précédentes.

RQ_IMPORT_DONNEES_01 a permis de construire la fonction générique d'importation fnImportMultipleTableSheet

La fonction fnIsExistsTableSheet permet de vérifier soit la présence d'une feuille soit la présence d'une table

let fnIsExistsTable =
    (
        pExceFile   as text,
        pItem       as text,
        pType       as text
    ) as any =>
        let
            Source = Excel.Workbook(File.Contents(pExceFile), null, true),
            TBL_FILTER = Table.SelectRows(Source, each ([Kind] = pType)),
            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

Fonction d'importation fnImportMultipleTableSheet

let fnImportMultipleTableSheet = (
    pType           as text,
    pPromote        as text,
    pTableCombine   as text,
    pTag            as text
) as table =>

        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 = fnIsExistsTableSheet(CurrentFile,CurrentItem, STR_TYPE),         // 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=STR_TYPE]}[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
                                                    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_TYPE=pType,
            STR_PROMOTE=pPromote,
            STR_TBL_COMBINE=pTableCombine,
            STR_TAG_PREM=pTag,

            //******************************************************************************
            // 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
in
    fnImportMultipleTableSheet

Exemples d'utilisation de la fonction d'importation

== Exemple 1 ==

Via la requête RQ_IMPORT_DONNEES_02
On passe par la table de paramètres comme suit :

let
    //******************************************************************************
    // Identification des paramètres
    //******************************************************************************
    STR_TYPE=fnGetParameter("TB_PARAMS", "TYPE"),
    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"),
    Source = fnImportMultipleTableSheet(STR_TYPE, STR_PROMOTE, STR_TBL_COMBINE, STR_TAG_PREM)
in
    Source

== Exemple 2 ==

Via les requêtes RQ_IMPORT_TABLE et RQ_IMPORT_SHEET
On directement les paramètres à l'appel de la fonction

let
    Source = fnImportMultipleTableSheet("Table", "NON", "TB_LIST_COMBINE_TABLE", "OUI")
in
    Source

let
    Source = fnImportMultipleTableSheet("Sheet", "OUI", "TB_LIST_COMBINE_SHEET", "OUI")
in
    Source

L'appel de la fonction sera donc selon votre contexte de développement.


A PROPOS DES FICHIERS D'EXEMPLES SOURCE VOIR LES POST PRECEDENTS ou utiliser les votres