How to Connect to an Excel File in SharePoint & Configure Scheduled Refresh in Power BI Service – The Right Way!
ONE QUESTION I AM ASKED OFTEN IS:
“I have connected to an Excel File in SharePoint. I am able to refresh data on Power BI Desktop. However, the refresh does not work on PowerBI.com”
If you connect to an Excel file located in SharePoint folder on your computer using the Excel connector, the file path points to the synced copy on your computer, not the copy on SharePoint online.
For scheduled refresh to work, there are 2 methods to connect to SharePoint files:
Method 1: Web Connector
Power BI Desktop Configuration
- Open the document in Excel Desktop
- Click on File > Info > Copy path
- In Power BI Desktop, click Get Data > Web
- Paste the file path into the URL box. Remove the text ?web=1 from the end of the copied path
- In the authentication settings dialogue box, select Organizational account, then select which level to apply the authentication settings to. The default level setting is the top-level web address. Sign in wih your Microsoft credentials.
If you select the top-level web address, the same authentication settings will be used for the URL and sub-address within the URL. The next time you use web connector to connect to files in the same SharePoint folder, you will not be prompted to key in the authentication methods. (Unless you clear the permission in the data source settings)
- On the Navigator window, select the worksheet(s) to connect and click Load or Transform Data.Power BI Service Configuration
- To configure the refresh settings in PowerBI.com, open settings of the connected dataset.
- If you on the report page, you can click on Click on the Ellipsis (three little dots), then See related content
- At the Data source credentials section, click Edit credentials. Choose OAuth2 as the authentication method and Organizational as the Privacy Level
- Click Sign in, then key in your Microsoft username and password
- For standard Power BI Pro (without Premium capacity) users, you can set up to 8 automatic refreshes a day.
- To manually refresh the dataset, click on the Refresh now button. Go to the report page. After the dataset is refreshed, either reload the browser or click Refresh visuals icon on the top right of Power BI report window
Method 2: SharePoint folder connector
Power BI Desktop Configuration
- On the Home tab, click on Get Data > File > SharePoint folder, then click Connect.
- Key in the SharePoint Site URL.
Note: Enter the root URL (not including the subfolders)
- When you are prompted for authentication, select “Microsoft account” and the level the setting is applied to.
On the Navigator window, click “Transform Data”
The course is demonstrated using Excel for Microsoft 365, but participants can use Excel 2016 or 2019 (Windows)
CHECK OUT MORE Upcoming Training Courses
YOU MAY also be INTERESTED IN
Advanced Data Analysis and Visualisation with Excel: Discover Strategic Business Insights
Strategic Business Analytics Masterclass: Techniques and Tools for Data-Driven Business Decisions in the Face of Uncertainty
Analyse and Visualise Data with Power BI Masterclass: Effective Dashboard Reporting and Business Intelligence for Strategic Insights
DAX Masterclass: Gain Insights from Data with Effective DAX Formulas for Common and Advanced Business Scenarios