📆 Published on Jan 17, 2025

6 min read

by Macktireh

Import Multiple Excel/CSV Files into Power BI with a Custom Power Query Function

---

Table des matières

• Introduction
• Using the “SharePoint Folder” Connector
  • Traditional Import Process
• Workaround with a Custom Power Query Function
  • Power Query Function Code
  • Adding the Function to Our Previous Example
• Using the function to import and combine Excel files
• Bonus – Adaptation for CSV Files
• Conclusion

Introduction

Importing and combining multiple Excel or CSV files into Power BI or Excel can quickly become a headache. Fortunately, Power Query offers connectors like “Folder” or “SharePoint Folder” to retrieve and merge files stored locally or on SharePoint/Teams. However, these connectors sometimes add too many unnecessary queries, which can complicate data management.

In this article, we will cover:

• How to use the “SharePoint folder” connector to import and combine files
• Why this method can become polluting
• A cleaner and optimized alternative using a custom Power Query function

Using the “SharePoint Folder” Connector

Generally, when you want to import and combine multiple CSV or Excel files, you use the “Folder” connector (for local files) or “SharePoint Folder” (for files stored on SharePoint/Teams).

Let’s take a concrete example: imagine you have several financial data files stored in a SharePoint folder, separated by country.

Traditional Import Process

1. In the Ribbon menu, click: Home > New Source > More...
2. Search for “SharePoint Folder”
3. Select it and click “Connect”
4. Enter your SharePoint site link (or use a Power Query parameter)

5. Click OK then “Transform Data”
6. In the “Folder Path” column, filter to select the folder containing your files
7. Click the double arrows in the first column to combine all files

🎯 Problem: At this stage, you’ll notice that Power Query creates numerous folders, queries, and functions. This is where it becomes problematic: with each similar import, you end up with a multiplication of elements that “pollute” your Power Query editor.

💡 Solution? Create a custom Power Query function!

Workaround with a Custom Power Query Function

The idea is to have a single clean and optimized query that imports and combines all files without unnecessary pollution. To solve this problem, I developed a utility Power Query function that allows importing and combining Excel files stored on SharePoint or Teams more cleanly.

Power Query Function Code

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

Detailed Function Explanation

📌 Function Signature

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

This function takes 5 parameters:

1. Source (table): The data source (typically a SharePoint or Teams folder).
2. folderPath (list): A list of subfolders to navigate to the target location.
3. fileExtension (text): The extension of files to import (e.g., "xlsx").
4. SheetOrTableName (text): The name of the target table in each Excel file (e.g., Excel sheet/table name).
5. filterColumnName (text): The name of the column that will be used to filter duplicate header rows (a column present in all files).

📌 Step Explanation

1️⃣ Navigate to the target folder

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

• Use List.Accumulate to navigate through the subfolders specified in folderPath.
• At each step, select the subfolder until reaching the final location.

2️⃣ Filter files by extension

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

• Filter files to keep only those with the specified extension (e.g., .xlsx).

3️⃣ Extract tables from each Excel file

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

• Use Excel.Workbook([Content]) to extract all tables and worksheets from Excel files.

4️⃣ Select only files and their tables

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

• Keep only two columns:
  • Name (file name)
  • Tables (file content)
• Rename Name to FileName to avoid confusion.

5️⃣ Expand the table column

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

• Expand the Tables column to extract table names and their data (Data).

6️⃣ Filter only the desired table

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

• Keep only the table whose name matches SheetOrTableName.

7️⃣ Expand the Data column (table data)

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,

• Retrieve the first row containing data (Data).
• Extract the column names.
• Unfold the table using the column names.

8️⃣ Remove the table name column

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

• Remove the Name column as it is no longer necessary.

9️⃣ Promote headers

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

• Use Table.PromoteHeaders to transform the first row into column headers.

🔟 Remove duplicate header rows

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

• Remove rows where the filterColumnName column contains its own name (these often correspond to header duplicates from Excel files).

1️⃣1️⃣ Rename the first column to FileName

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

• Identify the first column of the final table.
• Rename it to "FileName" to keep track of the data origin.

Adding the Function to Our Previous Example

1. In the ribbon menu, click Home > New Source > Blank Query.
2. Select the query and click Home > New Source > Advanced Editor.
3. Replace all the code with the code block above.
4. Rename the function as you wish. Personally, I name functions with explicit names, for example: fxImportAndCombineMultipleExcelFilesFromSharepointOrTeams

And that’s it! The function is ready to use. 🎉

Using the function to import and combine Excel files

To use our new function:

1. Create a new blank query (Home > New Source > Blank Query)
2. Open Advanced Editor
3. Copy and paste the function code
4. Rename as you wish. In this example, we’ll call it “MyData”

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

Explanation

1️⃣ Defining the SharePoint source:

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

• SharePoint.Contents: This function retrieves content from a specified SharePoint site.
• “YOUR_SHAREPOINT_SITE_LINK”: Replace this text with your SharePoint site URL.
• [ApiVersion = 15]: Specifies version 15 of the SharePoint API to use for data extraction.

2️⃣ Calling the fxImportAndCombineMultipleExcelFilesFromSharepointOrTeams function:

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

• Source: The previously defined source containing SharePoint site data.
• {"Shared Documents", "General", "FinancialSample"}: The folder path in SharePoint where Excel files are located.
• “xlsx”: The file extension to import (here, Excel files).
• “Sheet1”: The worksheet name to extract from each Excel file.
• “Segment”: The column used to filter rows in each file (in this example, the first column of our Excel files).

And there you have it, files are automatically imported and combined into a single query. Pretty clean and efficient, right? 😎

Bonus – Adaptation for CSV Files

For CSV files, here is a modified version of the function that takes a custom separator into account. Feel free to test it and adapt it to your specific needs:

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

📝 And there you go! You can now import and merge CSV files just as easily as Excel files. 🚀

Conclusion

In this article, we explored a workaround solution to efficiently import and combine multiple Excel/CSV files stored on SharePoint or Teams, using a custom Power Query function. This approach solves several common problems encountered when importing multiple files:

• Reducing Power Query editor pollution
• Simplifying the file import process
• Creating a unique and reusable method for data import

Feel free to adapt these functions to your needs and share your improvements in the comments! I’ll be delighted to enrich my collection of utility functions with your contributions. 😊