Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

POWERQUERY :: Combiner un dossier Excel comportant des fichiers avec des onglets (tableaux structurés) de même structure

oguruma

XLDnaute Occasionnel
L'idée cible est la suivante - sur la base de ventes de produits par mois et par canaux :



et on retrouve ce même de tableau dans les onglets suivants


et dans un dossier nous avons 3 fichiers avec cette structure


Le souhait est de totaliser les produits par mois pour ces 3 fichiers


Pour ces 3 fichiers on a fait la sommes des onglets pour chaque mois

L'autre cible est aussi de se passer de l'assistant pour plus de liberté.

Environnement PowerQuery


La solution - développement d'une fonction - fnCombineMultiTablesEXCELGroupBy_H


Elle est largement documentée dans le code.

Dans celle-ci on creuse comme vous pouvez le voir les capacités de développement en PowerQuery.

PowerQuery:
let

    //-------------------------------------------------------------------------------------------------------------------------------------
    // Fonction permettant de combiner plusieurs classeurs Excel avec plusieurs onglets
    // Recommandations :
    // Les fichiers Excel doivent avoir tous la même structure
    // Les données doivent être organisées dans des tableaux structurés
    // Pour des besoins de filtrages éventuels sur les noms de TS il est recommandé qu'ils débutent tous par le même préfixe
    // Exemple de structure
    // IDPROD     Janv    Fev     Mars    Avr     Mai     Juin    Juil    Aout    Sep     Oct     Novb      Decb
    // P0001      105     190     107     100     1580    250     300     256     147     123     856       145
    // Dans ce cas pRightOffset = 1
    // pSortColumn = "IDPROD"
    // pGroupByColumn = "IDPROD"
    // pFilesFilter : permet de filtrer sur les noms de fichiers dans le dossier y compris les dossiers
    // pFilter : permet de filtrer sur les tables (noms des tableaux structurés)
    //
    // Autre exemple
    // -------------
    // IDPROD   LIBELLE        Janv    Fev     Mars    Avr     Mai     Juin    Juil    Aout    Sep     Oct     Novb      Decb
    // P0001    LIB1           105     190     107     100     1580    250     300     256     147     123     856       145
    // Dans ce cas pRightOffset = 2 (pour prendre compte IDPROD et LIBELLE)
    // pSortColumn = "IDPROD"
    // pGroupByColumn = "IDPROD;LIBELLE" --> cela va constituer une liste de colonnes - ";" est le séparateur imposé
    // pRightOffset : correspond donc au nombre de colonnes titres à reprendre dans le GroupBy
    //-------------------------------------------------------------------------------------------------------------------------------------

    fnCombineMultiTablesEXCELGroupBy_H =(
        pFolder as text,
        pRightOffset as number,
        pKind as text,
        pSortColumn as text,
        pGroupByColumn as text,
        optional pFilesFilter as text,
        optional pFilter as text
    ) as table =>

    let
        //-----------------------------------------------------------------------------
        // On récupère le contenu du dossier
        //-----------------------------------------------------------------------------
        Source = Folder.Files(pFolder),
        FilterSource = if pFilesFilter is null then
                        Source
                        else
                            let
                                TblSource1=Table.SelectRows(Source, each Text.StartsWith([Name], pFilesFilter)),
                                TblSource2=if Table.IsEmpty(TblSource1) then null else TblSource1
                            in
                                TblSource2,

        //---------------------------------------------------------------------------------------------------
        // Fonction permettant de construire la table
        // L'appel de cette fonction permet de sortir de la requête proprement si aucune donnée à traiter
        // Cette fonction joue un peu le rôle d'une fonction ou sub en VBA
        //---------------------------------------------------------------------------------------------------
        BuildTable = (Content as any) =>
            let
                //-----------------------------------------------------------------------------
                // Pour chaque binaire on récupère les tables (tableaux structurés)
                // On fait appel à une boucle pour traiter la liste des binaires à fusionner
                //-----------------------------------------------------------------------------
                TbListCombine = List.Accumulate(
                                    Content,          // Liste des binaires à combiner
                                    #table({},{}),    // Initialisation avec une table vide
                                    (state,current) =>  // Lancement de la boucle
                                            let
                                                Classeur=Excel.Workbook(current), // On récupère le binaire
                                                Filter=Table.SelectRows(Classeur, each ([Kind] = pKind)), // On retient le type désiré (Table)
                                                CombineFilter=Table.Combine({state,Filter}) // Les binaires sont mis bout à bout
                                            in
                                                CombineFilter                              
                                ),

                //-----------------------------------------------------------------------------
                // Doit-on appliquer un filtre sur les tables à retenir                
                //-----------------------------------------------------------------------------
                FilterTable = if pFilter is null then
                                TbListCombine
                                else Table.SelectRows(TbListCombine, each Text.StartsWith([Item], pFilter)),  

                //-----------------------------------------------------------------------------
                // On extrait le les colonnes du 1er classeur qui va servir de référence
                //-----------------------------------------------------------------------------
                ListData=FilterTable[Data],
                ListColumns=Table.ColumnNames(ListData{0}),

                //-----------------------------------------------------------------------------
                // On récupère les data dans les binaires pour les convertir en table
                //-----------------------------------------------------------------------------
                ToData = Table.ExpandTableColumn(FilterTable, "Data", ListColumns),

                //-----------------------------------------------------------------------------
                // On ne retient que les colonnes de référence
                //-----------------------------------------------------------------------------
                ToSelectColumns = Table.SelectColumns(ToData,ListColumns),
               
                //-----------------------------------------------------------------------------
                // Tri selon les n premières colonnes de gauche retenues
                //-----------------------------------------------------------------------------
                Sort = Table.Sort(ToSelectColumns,{{pSortColumn, Order.Ascending}}),

                //-----------------------------------------------------------------------------
                // Construction des totaux par colonne selon le regroupement
                // Pour la totalisation des colonnes on passe à nouveau par une boucle
                //-----------------------------------------------------------------------------
                ListRight=List.LastN(ListColumns,List.Count(ListColumns)-pRightOffset),
                ToStringTotal=List.Accumulate(
                                        ListRight, // Liste des colonnes à cumuler
                                        "",        // Initialisation de l'accumulateur
                                        (state,current) => // Lancement de la boucle
                                            let
                                                acc=if state = "" then "" else state & ", ", // pour éviter une virgule de trop en tête
                                                // L'accumulateur sous forme de chaine pour l'évaluer en expression PowerQuery à l'issue
                                                Tot=acc & "{""" & current & """,  each List.Sum([" & current & "]), type number}"                                                                
                                            in
                                                Tot
                            ),

                //-----------------------------------------------------------------------------
                // Evaluation pour application dans le regroupement des totaux
                //-----------------------------------------------------------------------------
                ToEvalTotal=Expression.Evaluate("{" & ToStringTotal & "}",[List.Sum=List.Sum]),

                //-----------------------------------------------------------------------------
                // Résultat avec identification des colonnes titres à gauche des cumuls
                //-----------------------------------------------------------------------------
                ListLeftColumns=Text.Split(pGroupByColumn,";"),
                ToTableFinalGroupBy = Table.Group(Sort, ListLeftColumns, ToEvalTotal)
            in
                ToTableFinalGroupBy,

        //-----------------------------------------------------------------------------
        // On recupère les binaires des fichiers présents dans le dossier
        // et on construit la table
        //-----------------------------------------------------------------------------
        ToTableFinalGroupBy = if FilterSource is null then null else BuildTable(FilterSource[Content])
       
    in
        //-----------------------------------------------------------------------------
        // Si nous n'obtenons rien car aucune donnée on renvoie une table vide
        //-----------------------------------------------------------------------------      
        if ToTableFinalGroupBy is null then #table({},{}) else ToTableFinalGroupBy                      
in
    fnCombineMultiTablesEXCELGroupBy_H

Utilisation de la fonction

PowerQuery:
let
    Source =  fnCombineMultiTablesEXCELGroupBy_H (
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        1,
        "Table",
        "PRODUIT",
        "PRODUIT"
       
        //optional pFilesFilter as text,
        //optional pFilter as text
    )

in
    Source
   
let
    Source =  fnCombineMultiTablesEXCELGroupBy_H (
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        1,
        "Table",
        "PRODUIT",
        "PRODUIT",
        "VENTES"
        //optional pFilter as text
    )

in
    Source
   
let
    Source =  fnCombineMultiTablesEXCELGroupBy_H (
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        1,
        "Table",
        "PRODUIT",
        "PRODUIT",
       // "VENTES",
       null,
        "VTab"
    )

in
    Source
   
let
    Source =  fnCombineMultiTablesEXCELGroupBy_H (
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        1,
        "Table",
        "PRODUIT",
        "PRODUIT",      
        "VENTES",
        "Tab"
    )

in
    Source
   
let
    Source =  fnCombineMultiTablesEXCELGroupBy_H (
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES\xVENTES",
        2,
        "Table",
        "PRODUIT",
        "PRODUIT;LIBELLE",      
        null,
        null
    )

in
    Source
   
let
    Source =  fnCombineMultiTablesEXCELGroupBy_H (
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES\xVENTES",
        2,
        "Table",
        "PRODUIT",
        "PRODUIT;LIBELLE",      
        "xxVENTES",
        "Tab"
    )

in
    Source

Le dernier appel de la fonction renvoie ceci (table vide car aucune donnée n'a été trouvée)
 

Pièces jointes

  • VENTES.zip
    222.4 KB · Affichages: 2
  • FilesCombineMultiSheetsEXCEL_V0.0100.xlsx
    118.2 KB · Affichages: 2

oguruma

XLDnaute Occasionnel
Dans ce post une variante : on ne désire pas regrouper les données pour les totaliser mais plutôt conserver le détail de chaque fichier dans un seul et uniquement classeur Excel pour construire par exemple des TCD personnalisés.

Voici la fonction qui est encore largement de documentée avec une structure de code je dirai assez pédagogique pour les débutants en powerquery.

fnCombineMultiTablesEXCEL

PowerQuery:
let
    fnCombineMultiTablesEXCEL = (
            pFolder                as text,
            optional pFolderFilter as any,
            optional pFilesFilter  as any,
            optional pFilterItem   as any,
            optional pColTechnic   as any,
            optional pSortField    as any
    ) as table =>

        let

            //--------------------------------------------------------------------------------------------------
            // Fonction permettant de chager le binaire d'un classeur Excel
            //--------------------------------------------------------------------------------------------------
            LoadContents = (ParamContents) =>
                let
                    Src=Excel.Workbook(ParamContents, null, true)
                in
                    Src,

            //--------------------------------------------------------------------------------------------------
            // Fonction permettant de chager le dossier
            //--------------------------------------------------------------------------------------------------
            GetSource = () as any =>
                let
                    GetSrc=try Folder.Files(pFolder) otherwise null
                in
                    GetSrc,

            //--------------------------------------------------------------------------------------------------
            // Fonction permettant de construire la table
            //--------------------------------------------------------------------------------------------------
            Build = () as any =>
                let        

                    //--------------------------------------------------------------------------------------------------
                    // Filtre sur les noms de fichiers
                    //--------------------------------------------------------------------------------------------------
                    FilterSource = if pFilesFilter is null or pFilesFilter = false then
                                    Source
                                    else
                                        //---------------------------------------------------------------------------------
                                        // Bloc de traitements quand plusieurs instructions sont nécessaires
                                        //---------------------------------------------------------------------------------
                                        let
                                            //-----------------------------------------------------------------------------
                                            // On va en plus vérifie si la table est vide
                                            //-----------------------------------------------------------------------------
                                            // On remarque ici que dans une instruction if then else
                                            // on peut placer un bloc d'instructions via let et in
                                            //-----------------------------------------------------------------------------
                                            TblSource1=Table.SelectRows(Source, each Text.StartsWith([Name], pFilesFilter)),
                                            TblSource2=if Table.IsEmpty(TblSource1) then #table({},{}) else TblSource1
                                        in
                                            TblSource2,

                    //--------------------------------------------------------------------------------------------------
                    // Suppression des colonnes techniques inutiles
                    //--------------------------------------------------------------------------------------------------
                    DelOtherColumns = if Table.IsEmpty(FilterSource) then
                                         #table({},{})
                                         else Table.SelectColumns(FilterSource,{"Content", "Name", "Folder Path"}),

                    //--------------------------------------------------------------------------------------------------
                    // Cas des fichiers cachés
                    //--------------------------------------------------------------------------------------------------
                    FilterHidden = Table.SelectRows(DelOtherColumns, each [Attributes]?[Hidden]? <> true),  

                    //--------------------------------------------------------------------------------------------------
                    // Transformation des binaires
                    //--------------------------------------------------------------------------------------------------
                    CallFunction = Table.AddColumn(FilterHidden, "TransformFile", each LoadContents([Content]?)),

                    //--------------------------------------------------------------------------------------------------
                    // Extraction des binaires
                    //--------------------------------------------------------------------------------------------------
                    Expand1 = Table.ExpandTableColumn(CallFunction, "TransformFile",
                                    {"Name", "Data", "Item", "Kind", "Hidden"},
                                    {"TableExcel", "Expand1.Data", "Expand1.Item", "Expand1.Kind", "Expand1.Hidden"}),

                    //--------------------------------------------------------------------------------------------------
                    // Filtre sur le dossier à retenir
                    //--------------------------------------------------------------------------------------------------
                    FilterToFolder = if pFolderFilter is null or pFolderFilter = false then
                                        Expand1
                                        else Table.SelectRows(Expand1, each Text.StartsWith([Folder Path], pFolderFilter)),

                    //--------------------------------------------------------------------------------------------------
                    // Renommage des colonnes techniques qui seront conservées si besoin
                    //--------------------------------------------------------------------------------------------------
                    RenameColumnsExcel = Table.RenameColumns(FilterToFolder,{{"Name", "ClasseurExcel"}, {"Folder Path", "DossierExcel"}}),

                    //--------------------------------------------------------------------------------------------------
                    // Filtre sur le nom des tableaux structurés
                    //--------------------------------------------------------------------------------------------------
                    FilterLinesTable = if pFilterItem is null or pFilterItem = false then
                                        Table.SelectRows(RenameColumnsExcel, each ([Expand1.Kind] = "Table"))
                                        else let
                                                    //------------------------------------------------------------------------------
                                                    // Procédure de Filtrage sur le Kind et Item - bloc d'instrcutions
                                                    //------------------------------------------------------------------------------
                                                    SelKind=Table.SelectRows(RenameColumnsExcel, each ([Expand1.Kind] = "Table")),
                                                    SelItem=Table.SelectRows(RenameColumnsExcel, each (Text.StartsWith([Expand1.Item], pFilterItem)))
                                                in
                                                    SelItem,                                                  

                    //--------------------------------------------------------------------------------------------------
                    // Extraction des colonnes titre à partir de la 1ère table
                    //--------------------------------------------------------------------------------------------------
                    FirstTable=FilterLinesTable[Expand1.Data]{0},
                    ListColumns=Table.ColumnNames(FirstTable),

                    //--------------------------------------------------------------------------------------------------
                    // On ne retient que les colonnes concernées
                    //--------------------------------------------------------------------------------------------------
                    Expand2 = Table.ExpandTableColumn(FilterLinesTable, "Expand1.Data", ListColumns),

                    //--------------------------------------------------------------------------------------------------
                    // La colonne des binaires n'est plus utile
                    //--------------------------------------------------------------------------------------------------
                    DeleteBinaryContents = Table.RemoveColumns(Expand2,{"Content"}),

                    //--------------------------------------------------------------------------------------------------
                    // Transformation brute avec ajout ou non des colonnes techniques
                    //--------------------------------------------------------------------------------------------------
                    ToTable = if pColTechnic is null or pColTechnic = false then
                                Table.SelectColumns(DeleteBinaryContents,ListColumns)
                                else Table.SelectColumns(DeleteBinaryContents,{"ClasseurExcel", "DossierExcel", "TableExcel"} & ListColumns),

                    //--------------------------------------------------------------------------------------------------
                    // Traitement si nécessaire des critères de tri
                    //--------------------------------------------------------------------------------------------------
                    SortTable = if pSortField is null or pSortField = false then
                                ToTable
                                else let
                                            //--------------------------------------------------------------------------
                                            // Procédure de tri - bloc d'instructions
                                            //--------------------------------------------------------------------------
                                            pOrder=List.Repeat({0},List.Count(Text.Split(pSortField,";"))),
                                            pCrit=List.Zip({Text.Split(pSortField,";"),pOrder}),  
                                            ToSort=Table.Sort(ToTable,pCrit)
                                        in
                                            ToSort
                in
                    SortTable,

            //--------------------------------------------------------------------------------------------------
            // MAIN PROCEDURE ET APPEL DES FONCTIONS
            //--------------------------------------------------------------------------------------------------
            Source = GetSource(),
            ToBuild=if Source is null then
                    #table ({},{})
                    else Build()

        in
            ToBuild

in
    fnCombineMultiTablesEXCEL

Exemples d'utilisation
PowerQuery:
/* utilisation dans sa plus simple expression */
let
    Source = fnCombineMultiTablesEXCEL("D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES")
in
    Source
   
/* On filtre sous un sous-dossier si le dossier principal en comporte plusieurs */
let
    Source = fnCombineMultiTablesEXCEL(
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES"
   
    )
in
    Source
   

/* On filtre ici sur les fichiers à retenir */

let
    Source = fnCombineMultiTablesEXCEL(
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        "VENTES"
   
    )
in
    Source
   
/* On filtre sur les tableaux structurés */

let
    Source = fnCombineMultiTablesEXCEL(
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        "VENTES",
        "Table"
   
    )
in
    Source
   
/* On demande l'affichage des colonnes techniques - nom du fichier, dossier, nom du tableau

let
    Source = fnCombineMultiTablesEXCEL(
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        "VENTES",
        "Table",
        true
   
    )
in
    Source

/* autre filtre sur les noms de tableau */

let
    Source = fnCombineMultiTablesEXCEL(
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        "VENTES",
        "VTable",    //-> ici
        true
   
    )
in
    Source
   
/* on demande un tri sur des colonnes */

let
    Source = fnCombineMultiTablesEXCEL(
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        "VENTES",
        "Tableau",
        true,
        "PRODUIT;ClasseurExcel"  // --> tri sur ces colonnes
   
    )
in
    Source

/* pour les valeurs optionnelles elles sont soit de format texte, soit logical true/false, soit null */

let
    Source = fnCombineMultiTablesEXCEL(
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES",
        false,
        false,
        "Tableau",
        true,
        "PRODUIT;ClasseurExcel"
   
    )
in
    Source

/* autre dossier */

let
    Source = fnCombineMultiTablesEXCEL(
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\pqEXCEL\pqEXCEL_4\VENTES\xVENTES",
        false,
        false,
        null,
        true,
        "PRODUIT;ClasseurExcel"
   
    )
in
    Source
 

Pièces jointes

  • FilesCombineMultiSheetsEXCEL_V0.080_44.xlsx
    183.4 KB · Affichages: 2

oguruma

XLDnaute Occasionnel
Merci, je crois que je viens de faire un heureux... après c'est un début, la fonction est à adapter au besoin
 

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…