Share via

PBIWIKI: Connectors: Connecting to a single Excel File on SharePoint Online

  • Article

​* NOTE: This article is based on information discovered and the hope is that it helps lead you in the right direction to resolve the issue that you are currently working. PRODUCTS INVOLVED/ POWER BI FEATURES INVOLVED

  • Microsoft Power BI Desktop
  • Microsoft Power BI Service (Scheduled Refresh)
  • SharePoint Online
  • Microsoft Excel (XLSX)

PROBLEM SCENARIO DESCRIPTION

This scenario covers the ability to connect to a Microsoft Excel (XLSX) file that resides on SharePoint Online site. In working on a solution like this, we might come across different messages when either connecting and/or attempting to refresh the Microsoft Excel file.

In my specific scenario, my customer did not see this until we attempted to schedule a refresh.

ERROR MESSAGE

This message is displayed when attempting to Schedule a Refresh in the Power BI Service. We then go to enter the credentials.

https://dev.azure.com/Supportability/8ebe66d0-4ee2-4dba-9bd0-4b50aee067e3/_apis/git/repositories/9e88ab15-2724-4ae1-a0e5-a0032d87abf4/Items?path=%2F.attachments%2Fdatasourcecannotberefreshed-b441b7ee-f3b3-40a6-9184-e5adece292d6.png&download=false&resolveLfs=true&%24format=octetStream&api-version=5.0-preview.1&sanitize=true&versionDescriptor.version=wikiMaster

EXCEL FILE PATH EXAMPLES THAT DO NOT WORK

Copy Link Menu Item https://dev.azure.com/Supportability/8ebe66d0-4ee2-4dba-9bd0-4b50aee067e3/_apis/git/repositories/9e88ab15-2724-4ae1-a0e5-a0032d87abf4/Items?path=%2F.attachments%2Fexcel_copylinkitem-b65da281-3ef0-41af-b9c5-eed788b8549b.png&download=false&resolveLfs=true&%24format=octetStream&api-version=5.0-preview.1&sanitize=true&versionDescriptor.version=wikiMasterhttps://mycompanyname.sharepoint.com/:x :/t/my-excel-test/ETCnElhrOvBJtrAYPeMBJyYB_Xw8FyS4PeigPv82EAJ4jA?e=60ElaD This does not work for the Excel and/or Web Connector. Power BI does not understand the extra information in the URL and thus will throw an error if this is used.
URL From Excel Online https://mycompanyname.sharepoint.com/:x :/r/teams/my-excel-test/_layouts/15/Doc.aspx?sourcedoc=%7B5812A730-3A6B-49F0-B6B0-183DE3012726%7D&file=Book.xlsx&action=default&mobileredirect=true This does not work for the Excel and/or Web Connector. Power BI does not understand the extra information in the URL and thus will throw an error if this is used.
Copy path to clipboard https://dev.azure.com/Supportability/8ebe66d0-4ee2-4dba-9bd0-4b50aee067e3/_apis/git/repositories/9e88ab15-2724-4ae1-a0e5-a0032d87abf4/Items?path=%2F.attachments%2Fexcel_copypathtoclipboard-acd3363c-c4e5-4cd7-ac59-d3d41909284f.png&download=false&resolveLfs=true&%24format=octetStream&api-version=5.0-preview.1&sanitize=true&versionDescriptor.version=wikiMasterhttps://mycompanyname.sharepoint.com/teams/my-excel-test/Shared%20Documents/General/Book.xlsx?web=1 This does not work for the Excel and/or Web Connector. Power BI does not like the ?web=1 at the end of the URL. This is probably the easiest link to modify.
Good/Working URL This is covered in the Resolution section of this document.

CAUSE

The reason that we could not see the OAUTH2 in the drop down list, is because the URL that is being utilized to point to the Microsoft Excel (XLSX) file is not one that Power BI understands.

RESOLUTION

To resolve this issue, the bad URL path to the Excel Document stored on SharePoint Online, will need to be converted to a URL that is fully understandable to Power BI.

EXCEL DOCUMENT URL FORMAT

HTTPS SharePoint Online, will always require us to utilize HTTPS. If we do not utilize HTTPS, we will receive authentication errors.
SHAREPOINT ONLINE PATH This is the first part of the URL. A SharePoint Online URL, will always end in sharepoint.com . A good example is: mycompanyname.sharepoint.com 
SITE PATH In many occasions, documents maybe stored within a SharePoint Site. It is important to have that directly after the SharePoint Online Path. There are times, it may be two sites deep. A good example of the format for a SharePoint Site URL would be: mycompanyname.sharepoint.com/teams .

Resolving this issue, we had to modify the bad URL to be a URL that Power BI understands.

ADDITIONAL INFORMATION

​*NOTE: This article is based on information discovered and the hope is that it helps lead you in the right direction to resolve the issue that you are currently working.​