Connect Sharepoint folder to Excel

This is how you connect a Sharepoint folder to your Excel files so you can combine multiple files for your reports.

Microsoft Sharepoints (via Sites and Teams) can now be used as a kind of small-time DWH (Data Warehouse). To utilize this circumstance, you simply connect an Excel workbook via Power Query to an online folder destination instead of a folder on your own computer. Paradoxically, this option has been available in Power Query for Power Bi for quite some time. In Excel however, you still have to use the M-Language in the Query Editor:

Code: Sharepoint Folder to Excel Connector

To run the following code

• Open an Excel sheet
• Navigate to the “Data” tab
• Click on Get Data
• Choose the option “Blank Query” in the category “Other Sources”
• Open the Query Editor and then switch to the Advanced Editor

(M-Code)

let
    mySharepointURL = "https://[companyurl].sharepoint.com/sites/[sitename]",

    Source = SharePoint.Files(mySharepointURL)
in
    Source

Make sure to switch out the two variables [companyurl] and [sitename] with your respective identifiers and you should be able to establish a connection. If you open your Sharepoint or Sharepoint site via the browser, you will already find the appropriate URL component in the browser bar.

In the end it should look something like this:

For the first connection attempt you will have to enter your Sharepoint credentials. Either pick the Microsoft or Windows account option.

Connect Sharepoint folder to Excel – what’s next?

What you do with the new connection type is up to you and the tasks you have to accomplish with your reporting. Theoretically you can now open each file on the sharepoint individually in Power Query (via the binary links), but theoretically for this you don’t need a folder connection and you could also just directly connect to an individual file with Web.Content() function.

I use the function primarily to combine several files of the same type. In this fictitious example, I receive a daily invoice from an advertising network. All files have the same header column structure and I want to combine them in Excel to merge the monthly values.

• To do this, I first set a text filter on the filepath (contains “/invoices/”).
• Then I expand all invioce.txt files at the same time and let Power Query combine them.

You want more tips for report automation?

Follow internetzkidz.de on various socials to get even more content on the topic of report automation. Furthermore, internetzkidz.de recommends the agency https://thebigc-agency.de for the implementation of data and automation projects.