📆 Publié le 17 janv. 2025

6 min de lecture

par Macktireh

Importer Plusieurs Fichiers Excel/CSV dans Power BI avec une Fonction Power Query Personnalisée

---

Table des matières

• Introduction
• Utilisation du connecteur “Dossier SharePoint”
  • Processus d’importation traditionnel
• Solution de contournement avec une fonction Power Query personnalisée
  • Code de la fonction Power Query
  • Ajout de la fonction dans notre exemple précédent
• Utilisation de la fonction pour importer et combiner des fichiers Excel
• Bonus – Adaptation pour les fichiers CSV
• Conclusion

Introduction

Importer et combiner plusieurs fichiers Excel ou CSV dans Power BI ou Excel peut rapidement devenir un casse-tête. Heureusement, Power Query propose des connecteurs comme “Dossier” ou “Dossier SharePoint” pour récupérer et fusionner des fichiers stockés localement ou sur SharePoint/Teams. Cependant, ces connecteurs ajoutent parfois trop de requêtes inutiles, ce qui peut compliquer la gestion des données

Dans cet article, nous allons voir :

• Comment utiliser le connecteur “Dossier SharePoint” pour importer et combiner des fichiers
• Pourquoi cette méthode peut devenir polluante
• Une alternative plus propre et optimisée grâce à une fonction Power Query personnalisée

Utilisation du connecteur “Dossier SharePoint”

Généralement, lorsqu’on souhaite importer et combiner plusieurs fichiers CSV ou Excel, on utilise le connecteur “Dossier” (pour les fichiers locaux) ou “Dossier SharePoint” (pour les fichiers stockés sur SharePoint/Teams).

Prenons un exemple concret : imaginons que vous ayez plusieurs fichiers de données financières par pays stockés dans un dossier SharePoint.

Processus d’importation traditionnel

1. Dans le menu Ruban, cliquez sur : Accueil > Nouvelle Source > Plus...
2. Recherchez “Dossier SharePoint”
3. Sélectionnez-le et cliquez sur “Se connecter”
4. Saisissez le lien de votre site SharePoint (ou utilisez un paramètre Power Query)

5. Cliquez sur OK puis sur “Transformer les données”
6. Dans la colonne “Folder Path”, filtrez pour sélectionner le dossier contenant vos fichiers
7. Cliquez sur les doubles flèches de la première colonne pour combiner tous les fichiers

🎯 Problème : À ce stade, vous remarquerez que Power Query crée de nombreux dossiers, requêtes et fonctions. C’est là que ça devient problématique : à chaque nouvelle importation similaire, vous vous retrouvez avec une multiplication de ces éléments qui “polluent” votre éditeur Power Query.

💡 Solution ? Créer une fonction Power Query personnalisée !

Solution de contournement avec une fonction Power Query personnalisée

L’idée est d’avoir une seule requête propre et optimisée qui importe et combine tous les fichiers, sans pollution inutile. Pour résoudre ce problème, j’ai développé une fonction Power Query utilitaire qui permet d’importer et de combiner des fichiers Excel stockés sur SharePoint ou Teams de manière plus propre.

Code de la fonction Power Query

1
let
2
    ImportAndCombineExcelFiles = (
3
        Source as table, folderPath as list, fileExtension as text, SheetOrTableName as text, filterColumnName as text
4
    ) =>
5
        let
6
            NavigateToFolder = List.Accumulate(
7
                folderPath, Source, (current, item) => current{[Name = item]}[Content]
8
            ),
9
            FilterByExtension = Table.SelectRows(NavigateToFolder, each ([Extension] = "." & fileExtension)),
10
            AddWorkbookContent = Table.AddColumn(FilterByExtension, "Tables", each Excel.Workbook([Content])),
11
            KeepFileNameAndTables = Table.SelectColumns(AddWorkbookContent, {"Name", "Tables"}),
12
            RenameFileNameColumn = Table.RenameColumns(KeepFileNameAndTables, {{"Name", "FileName"}}),
13
            ExpandTablesColumn = Table.ExpandTableColumn(
14
                RenameFileNameColumn, "Tables", {"Name", "Data"}, {"Name", "Data"}
15
            ),
16
            FilterByTableName = Table.SelectRows(ExpandTablesColumn, each ([Name] = SheetOrTableName)),
17
            ExpandDataColumn =
18
                let
19
                    data = FilterByTableName{0}[Data],
20
                    columns = Table.ColumnNames(data),
21
                    expanded = Table.ExpandTableColumn(FilterByTableName, "Data", columns, columns)
22
                in
23
                    expanded,
24
            RemoveNameColumn = Table.RemoveColumns(ExpandDataColumn, {"Name"}),
25
            PromoteHeaders = Table.PromoteHeaders(RemoveNameColumn, [PromoteAllScalars = true]),
26
            FilterOutHeaderRows = Table.SelectRows(
27
                PromoteHeaders, each (Record.Field(_, filterColumnName) <> filterColumnName)
28
            ),
29
            firstColumnName = Table.ColumnNames(FilterOutHeaderRows){0},
30
            RenameFirstColumn = Table.RenameColumns(FilterOutHeaderRows, {{firstColumnName, "FileName"}})
31
        in
32
            RenameFirstColumn
33
in
34
    ImportAndCombineExcelFiles

Explication détaillée de la fonction

📌 Signature de la fonction

1
ImportAndCombineExcelFiles = (
2
    Source as table, folderPath as list, fileExtension as text, SheetOrTableName as text, filterColumnName as text
3
) => ...

Cette fonction prend 5 paramètres :

1. Source (table) : La source de données (généralement un SharePoint ou un dossier Teams).
2. folderPath (list) : Une liste des sous-dossiers permettant de naviguer jusqu’à l’emplacement cible.
3. fileExtension (text) : L’extension des fichiers à importer (ex. "xlsx").
4. SheetOrTableName (text) : Le nom de la table cible dans chaque fichier Excel (ex: nom de la feuille/table Excel).
5. filterColumnName (text) : Le nom de la colonne qui servira à filtrer les lignes d’en-tête en double (une colonne qui est présente dans tous les fichiers).

📌 Explication des étapes

1️⃣ Naviguer jusqu’au dossier cible

1
NavigateToFolder = List.Accumulate(
2
    folderPath, Source, (current, item) => current{[Name = item]}[Content]
3
),

• On utilise List.Accumulate pour naviguer dans les sous-dossiers spécifiés dans folderPath.
• À chaque étape, on sélectionne le sous-dossier jusqu’à atteindre l’emplacement final.

2️⃣ Filtrer les fichiers par extension

1
FilterByExtension = Table.SelectRows(NavigateToFolder, each ([Extension] = "." & fileExtension)),

• On filtre les fichiers pour ne garder que ceux ayant l’extension spécifiée (ex. .xlsx).

3️⃣ Extraire les tables de chaque fichier Excel

1
AddWorkbookContent = Table.AddColumn(FilterByExtension, "Tables", each Excel.Workbook([Content])),

• On utilise Excel.Workbook([Content]) pour extraire toutes les tables et feuilles de calcul des fichiers Excel.

4️⃣ Sélectionner uniquement les fichiers et leurs tables

1
KeepFileNameAndTables = Table.SelectColumns(AddWorkbookContent, {"Name", "Tables"}),
2
RenameFileNameColumn = Table.RenameColumns(KeepFileNameAndTables, {{"Name", "FileName"}}),

• On garde uniquement deux colonnes :
  • Name (nom du fichier).
  • Tables (le contenu du fichier).
• On renomme Name en FileName pour éviter toute confusion.

5️⃣ Déplier la colonne contenant les tables

1
ExpandTablesColumn = Table.ExpandTableColumn(
2
    RenameFileNameColumn, "Tables", {"Name", "Data"}, {"Name", "Data"}
3
),

• On déplie la colonne Tables pour extraire les noms des tables et leurs données (Data).

6️⃣ Filtrer uniquement la table souhaitée

1
FilterByTableName = Table.SelectRows(ExpandTablesColumn, each ([Name] = SheetOrTableName)),

• On garde uniquement la table dont le nom correspond à SheetOrTableName.

7️⃣ Déplier la colonne Data (les données de la table)

1
ExpandDataColumn =
2
    let
3
        data = FilterByTableName{0}[Data],
4
        columns = Table.ColumnNames(data),
5
        expanded = Table.ExpandTableColumn(FilterByTableName, "Data", columns, columns)
6
    in
7
        expanded,

• On récupère la première ligne contenant les données (Data).
• On extrait les noms des colonnes.
• On déplie la table en utilisant les noms des colonnes.

8️⃣ Supprimer la colonne du nom de la table

1
RemoveNameColumn = Table.RemoveColumns(ExpandDataColumn, {"Name"}),

• On supprime la colonne Name car elle n’est plus nécessaire.

9️⃣ Promouvoir les en-têtes

1
PromoteHeaders = Table.PromoteHeaders(RemoveNameColumn, [PromoteAllScalars = true]),

• On utilise Table.PromoteHeaders pour transformer la première ligne en en-têtes de colonne.

🔟 Supprimer les lignes d’en-tête en double

1
FilterOutHeaderRows = Table.SelectRows(
2
    PromoteHeaders, each (Record.Field(_, filterColumnName) <> filterColumnName)
3
),

• On supprime les lignes où la colonne filterColumnName contient son propre nom (celles-ci correspondent souvent à des doublons d’en-tête provenant des fichiers Excel).

1️⃣1️⃣ Renommer la première colonne en FileName

1
firstColumnName = Table.ColumnNames(FilterOutHeaderRows){0},
2
RenameFirstColumn = Table.RenameColumns(FilterOutHeaderRows, {{firstColumnName, "FileName"}})

• On identifie la première colonne du tableau final.
• On la renomme en "FileName" pour garder une trace de l’origine des données.

Ajout de la fonction dans notre exemple précédent

1. Dans le menu ruban, cliquez sur Accueil > Nouvelle Source > Requête vide.
2. Sélectionnez la requête et cliquez sur Accueil > Nouvelle Source > Éditeur avancé.
3. Remplacez tout le code par le bloc de code ci-dessus.
4. Renommez la fonction comme vous le souhaitez. Personnellement, je nomme les fonctions avec des noms explicites, par exemple : fxImportAndCombineMultipleExcelFilesFromSharepointOrTeams

C’est fait ! La fonction est prête à être utilisée. 🎉

Utilisation de la fonction pour importer et combiner des fichiers Excel

Pour utiliser notre nouvelle fonction :

1. Créez une nouvelle requête vide (Accueil > Nouvelle Source > Requête vide)
2. Ouvrez l’Éditeur avancé
3. Copiez-collez le code de la fonction
4. Renommer comme vous le souhaitez. Dans cet exemple, nous l’appelons “MyData”

1
let
2
    Source = SharePoint.Contents("VOTRE_LIEN_DU_SITE_SHAREPOINT", [ApiVersion = 15]),
3
    ImportAndCombineFiles = fxImportAndCombineMultipleExcelFilesFromSharepointOrTeams(
4
        Source, {"Documents partages", "General", "FinancialSample"}, "xlsx", "Sheet1", "Segment"
5
    )
6
in
7
    ImportAndCombineFiles

Explication

1️⃣ Définition de la source SharePoint :

1
Source = SharePoint.Contents("VOTRE_LIEN_DU_SITE_SHAREPOINT", [ApiVersion = 15])

• SharePoint.Contents : Cette fonction permet de récupérer le contenu d’un site SharePoint spécifié.
• “VOTRE_LIEN_DU_SITE_SHAREPOINT” : Remplace ce texte par l’URL de ton site SharePoint.
• [ApiVersion = 15] : Spécifie la version 15 de l’API SharePoint à utiliser pour l’extraction des données.

2️⃣ Appel de la fonction fxImportAndCombineMultipleExcelFilesFromSharepointOrTeams :

1
ImportAndCombineFiles = fxImportAndCombineMultipleExcelFilesFromSharepointOrTeams(
2
    Source, {"Documents partages", "General", "FinancialSample"}, "xlsx", "Sheet1", "Segment"
3
)

• Source : La source définie précédemment, qui contient les données du site SharePoint.
• {"Documents partages", "General", "FinancialSample"} : Le chemin du dossier dans SharePoint où se trouvent les fichiers Excel à importer.
• “xlsx” : L’extension des fichiers à importer (ici, des fichiers Excel).
• “Sheet1” : Le nom de la feuille de calcul à extraire de chaque fichier Excel.
• “Segment” : La colonne utilisée pour filtrer les lignes dans chaque fichier (dans cet exemple, il s’agit de la première colonne de nos fichiers Excel).

Et voilà, les fichiers sont automatiquement importés et combinés dans une seule requête. C’est vraiment propre et efficace, non ? 😎

Bonus – Adaptation pour les fichiers CSV

Pour les fichiers CSV, voici une version modifiée de la fonction qui prend en compte un séparateur personnalisé. N’hésitez pas à la tester et à l’adapter selon vos besoins spécifiques :

1
let
2
    ImportAndCombineCSVFiles = (Source as table, folderPath as list, fileExtension as text, separator as text, filterColumnName as text) =>
3
        let
4
            NavigateToFolder = List.Accumulate(
5
                folderPath, Source, (current, item) => current{[Name = item]}[Content]
6
            ),
7
            FilterByExtension = Table.SelectRows(NavigateToFolder, each ([Extension] = "." & fileExtension)),
8
            AddCSVContent = Table.AddColumn(
9
                FilterByExtension, "Tables", each Csv.Document([Content], [Delimiter = separator])
10
            ),
11
            KeepFileNameAndTables = Table.SelectColumns(AddCSVContent, {"Name", "Tables"}),
12
            RenameFileNameColumn = Table.RenameColumns(KeepFileNameAndTables, {{"Name", "FileName"}}),
13
            ExpandTablesColumn =
14
                let
15
                    data = RenameFileNameColumn{0}[Tables],
16
                    columns = Table.ColumnNames(data),
17
                    expanded = Table.ExpandTableColumn(RenameFileNameColumn, "Tables", columns, columns)
18
                in
19
                    expanded,
20
            PromoteHeaders = Table.PromoteHeaders(ExpandTablesColumn, [PromoteAllScalars = true]),
21
            FilterOutHeaderRows = Table.SelectRows(
22
                PromoteHeaders, each (Record.Field(_, filterColumnName) <> filterColumnName)
23
            ),
24
            firstColumnName = Table.ColumnNames(FilterOutHeaderRows){0},
25
            RenameFirstColumn = Table.RenameColumns(FilterOutHeaderRows, {{firstColumnName, "FileName"}})
26
        in
27
            RenameFirstColumn
28
in
29
    ImportAndCombineCSVFiles

📝 Et voilà ! Vous pouvez maintenant importer et fusionner des fichiers CSV aussi facilement que les fichiers Excel. 🚀

Conclusion

Dans cet article, nous avons exploré une solution de contournement pour importer et combiner efficacement plusieurs fichiers Excel/CSV stockés sur SharePoint ou Teams, en utilisant une fonction Power Query personnalisée. Cette approche résout plusieurs problèmes courants rencontrés lors de l’importation de fichiers multiples :

• Réduction de la pollution de l’éditeur Power Query
• Simplification du processus d’importation de fichiers
• Création d’une méthode unique et réutilisable pour l’importation de données

N’hésitez pas à adapter ces fonctions selon vos besoins et à partager vos améliorations dans les commentaires ! Je serai ravi d’enrichir ma collection de fonctions utilitaires avec vos contributions. 😊