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.  

CHECK OUT MORE Upcoming Training Courses

YOU MAY also be INTERESTED IN