Combine Files from SharePoint folder and sub folders ( Easily enter a File Path )

Introduction

In this tutorial, we will explore how to combine files from a SharePoint folder and its subfolders using various methods in Power BI and Power Query. This guide aims to simplify the process of importing and consolidating data from multiple sources, which is particularly useful for analysts and data professionals working with SharePoint data.

Step 1: Basic Method to Access Files

1. Navigate to SharePoint: Open your SharePoint site and locate the folder containing the files you want to combine.
2. Copy the File Path:
   • Right-click on the folder and select “Copy Link” to get the URL.
   • Ensure the path includes the necessary credentials if required.
3. Import into Power BI:
   • Open Power BI Desktop.
   • Go to the Home tab and select “Get Data.”
   • Choose “Web” and paste the copied file path.
4. Load Data:
   • Click “OK” and allow Power BI to access the files.
   • Select the files you want to combine and load them into your report.

Step 2: Using List.Transform for Advanced File Access

1. Open Power Query Editor:
   • In Power BI, navigate to the “Transform Data” option to launch the Power Query Editor.
2. Apply List.Transform:
   • Use the following code snippet to apply List.Transform to your file list:

   let
       Source = SharePoint.Files("https://yoursharepointsite"),
       FilteredFiles = Table.SelectRows(Source, each [Extension] = ".xlsx"),
       CombinedFiles = Table.Combine(FilteredFiles)
   in
       CombinedFiles
   

   • Replace "https://yoursharepointsite" with your actual SharePoint URL.
3. Load Combined Data: After executing the transformation, load the combined data into Power BI.

Step 3: Creating a Custom Function to Access Subfolders

1. Define the Custom Function:
   • In the Power Query Editor, go to the “Home” tab and select “Advanced Editor”.
   • Use the following code to create a custom function that retrieves files from both folders and subfolders:

   let
       GetFiles = (folderPath as text) =>
       let
           Source = SharePoint.Files(folderPath),
           FilteredFiles = Table.SelectRows(Source, each [Extension] = ".xlsx")
       in
           FilteredFiles
   in
       GetFiles
   

2. Invoke the Function:
   • Call the function by providing the path of the main folder.
   • For example:

   CombinedData = GetFiles("https://yoursharepointsite/yourfolder")
   

3. Combine Data: After invoking the custom function, you can further combine the results as needed.

Conclusion

By following these steps, you can easily combine files from SharePoint folders and their subfolders using Power BI and Power Query. Whether you choose the basic method, utilize List.Transform, or create a custom function, these techniques will enhance your ability to manage and analyze data efficiently. For further exploration, consider implementing additional data transformations or integrating this process into automated workflows within Power BI.