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 PowerBI.com 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:
- 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:
- https://excelwithallisonmvp.sharepoint.com/sites/Training/Resources/PowerBIEssentials/Adventure%20Works%20Product%20Categories.xlsx?web=1
- Keep only the main part of the URL:
- https://excelwithallisonmvp.sharepoint.com/sites/Training/Resources/PowerBIEssentials/Adventure%20Works%20Product%20Categories.xlsx
- That's it - that's what you'll need to get data into Power BI
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
- Paste the URL from Step 1 (make sure you have deleted the ?web=1 if using Method 2)
- 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.
- 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.
- When you publish this to PowerBI.com you'll be able to schedule a refresh using your current credentials - no Gateway needed!
