Search this blog:

Search This Blog

Get Data from SharePoint Files into Power BI

Today I'm finally going to let you in on one of my best kept secrets - how to get data from SharePoint files into Power BI without referencing your local C:\ (or D:\ or E:\) drive. This will ensure that you can schedule refreshes on the SharePoint files you are using with Power BI.

How to Get Data from Excel or CSV file on SharePoint into Power BI (without a gateway)

Step 1: Get SharePoint filepath URL

In order to schedule a refresh in on files stored in SharePoint, you need to use the https:// URL. There are two main methods I use to grab this URL.

Method 1: From Teams or SharePoint

For this method to work, you must open the file in SharePoint. 

If you're starting in Teams:

  • Click the 'Open in SharePoint' button on the ribbon of the Files tab:

screenshot Open in SharePoint button location
  • This will take you to the SharePoint document library for that file. Continue following the steps below for SharePoint.

If you're starting in SharePoint:

  • Locate the file you want to import into Power BI.
  • Click the three dots next to the file
  • Select 'Details'
  • A new pane will open on the right hand side of your screen - scroll ALL the way to the bottom
  • Click the two sheets of paper next to the word 'Path' to copy the filepath
  • That's it - you'll use this to get data into Power BI

Method 2: From Excel or desktop app

This method is easy if you've already got the file open in the desktop app. 

  • Click File > Info
  • Click the 'Copy path' button
  • This URL will have a ?web=1 after it that you'll need to delete:
  • Keep only the main part of the URL:
  • That's it - that's what you'll need to get data into Power BI

screenshot Copy path file location

Step 2: Get data into Power BI (use Get data from Web)

Even though your data source is likely to be an Excel or CSV file, we are going to use the 'Get data from Web' option to get the data into Power BI.

This is because we have an https URL that points directly to our file. The last part of the URL is the filetype (such as .xlsx) and Power BI will use that to automatically use the correct functions to import the data correctly.

  • Click Get Data > Web
    screenshot Get Data from web
  • Paste the URL from Step 1 (make sure you have deleted the ?web=1 if using Method 2)

    screenshot URL
  • Click OK
  • If you haven't used this data source previously, you'll be prompted to enter credentials. 
    • Choose Organizational Account
    • Sign in with the same account that has access to the SharePoint file.
      screenshot enter credentials

    • Click Connect
  • That's it! 
    • The next screen you see will depend on the file type. For an Excel file, I see a navigator window asking which sheets or tables I want to import. 
    • Continue importing the data as you normally would for that filetype.
      screenshot Navigator window

  • When you publish this to you'll be able to schedule a refresh using your current credentials - no Gateway needed!

Custom Visual Review: Charticulator

This is not your ordinary custom visual - this is EVERY custom visual. Charticulator puts the power to design and develop custom visuals to ...