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

Article
1/17/2024

* 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=wikiMaster https://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=wikiMaster https://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.

Bad Hyperlink: https://mycompanyname.sharepoint.com/:x:/t/my-excel-test/ETCnElhrOvBJtrAYPeMBJyYB_Xw8FyS4PeigPv82EAJ4jA?e=60ElaD
**Power BI understanding URL: ** https://mycompanyname.sharepoint.com/my-excel-test/books1.xlsx

ADDITIONAL INFORMATION

Get data from files for Power BI:/en-us/power-bi/service-get-data-from-files
Get data from Excel Workbook Files:/en-us/power-bi/service-get-data-from-files
Connect to Excel in Power BI Desktop: /en-us/power-bi/desktop-connect-excel
Connect to CSV file in Power BI Desktop: /en-us/power-bi/desktop-connect-csv

*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.

Share via