fbpx

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”

ISSUE:

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.

SOLUTIONS:

For scheduled refresh to work, there are 2 methods to connect to SharePoint files:

Method 1: Web Connector

Power BI Desktop Configuration

  1. Open the document in Excel Desktop
  2. Click on File > Info > Copy path
  3. In Power BI Desktop, click Get Data > Web
  4. Paste the file path into the URL box. Remove the text ?web=1 from the end of the copied path
  5. 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)
  6. On the Navigator window, select the worksheet(s) to connect and click Load or Transform Data.Power BI Service Configuration
  7. To configure the refresh settings in PowerBI.com, open settings of the connected dataset.
  8. If you on the report page, you can click on Click on the Ellipsis (three little dots), then See related content
  9. At the Data source credentials section, click Edit credentials. Choose OAuth2 as the authentication method and Organizational as the Privacy Level

  10. Click Sign in, then key in your Microsoft username and password
  11. For standard Power BI Pro (without Premium capacity) users, you can set up to 8 automatic refreshes a day.
  12. 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

  1. On the Home tab, click on Get Data > File > SharePoint folder, then click Connect.
  2. Key in the SharePoint Site URL.
    Note: Enter the root URL (not including the subfolders)
  3. When you are prompted for authentication, select “Microsoft account” and the level the setting is applied to.
  4. On the Navigator window, click “Transform Data

  5. dd
  6.  

Upcoming workshops
  • dddd
Software Used

The course is demonstrated using Excel for Microsoft 365, but participants can use Excel 2016 or 2019 (Windows)