How to connect PBI to Excel file on Sharepoint

[croberts21]

I have my own XLSX test file on Sharepoint that I want PBI Desktop (May 2022) to import. (We use Import mode in PBI.) PBI runs on Windows Server 2019. We have Office 365 and a Microsoft account which is also used with PBI. I'm signed into PBI as indicated by the far right of the PBI title bar which shows my name and picture.

I got a direct link to the test Excel file which looks like this: https://myorg.sharepoint.com/sites/Mydept/Shared%20Documents/TestFile.xlsx. (This is a fake file and does not actually exist.)

In PBI I chose Get Data, Web, the entered the URL above and clicked OK. I clicked the Organizational Account, then Sign In and got an error "We're unable to connect. Please delete your credentials and try again later."

How do I delete my credentials if I'm not even signed in?

I have tried numerous ways to connect to the Excel file on Sharepoint, watched 3 videos, read 3 more pages on this and cannot seem to get it to work. Does anyone have an idea that they have tried that they know will work?

Thank you!

EDIT: A bit of info on this from MS is here: https://docs.microsoft.com/en-us/power-bi/connect-data/service-excel-workbook-files I don't see any problems based on the info in that page. 

[Anonymous]

Hi @croberts21 

After Get data, don't choose web.

Search for SharePoint folder and connect to it.

[croberts21]

I did Sharepoint Folder, and got the URL to the folder, and that didn't work. I also entered just the URL to the base site and that also didn't work. 

[croberts21]

I have credentials for Office 365 for work, and that's what I use to sign into the PBI Desktop app, to sign into Sharepoint on the web to get the direct links to the folder and Excel file, and to sign into the Sharepoint Folder connector in PBI.

This time I made a subfolder on my own Sharepoint site, and put a test Excel file in that folder. I got a link to the Sharepoint folder. In PBI I chose the connector "Sharepoint folder", entered the folder link, and on the popup Anonymous tab I got this error:

"Details: "Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)"

Then I cleared my permissions for all Sharepoint folders.

I restarted the process in PBI, chose the connector Sharepoint Folder, entered the direct URL to the Sharepoint folder, I chose the tab "Microsoft account", I clicked the "Sign in" button, and got an error "We're unable to connect. Please check your credentials and try again later."

On the same Sharepoint Folder popup window I then chose "Windows" tab, chose "Use my current credentials", clicked the Connect button and got this error:
"Details: "Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)"

[croberts21]

Ok, I have to ask the other person for the link to the Sharepoint folder that contains the Excel file. I'll get back to you on this.