Hi Kent,

The error message implies that the account you're logged into Excel with doesn't have permission to that SharePoint file/folder. The account you're logged into Power BI with must be different.

Mynda

Hi Mynda,

That was my initial thought as well, which is why I tried the PowerBI angle.  Then, I tried to connect to the same SharePoint site, in Excel, and was able to successfully connect to the folder.  So, I tried again to connect to the file, and was unsuccessful.  In Excel, I am able to connect to the folder, but not an individual file.  So, my permissions are ok, I am just doing something wrong in connecting to the file.  

There are some spaces in the folder names, so there is an entry of %20 in the path name instead of a space.  I tried to connect with the %20, and also with replacing the %20 with a space, neither of which was successful.  

Also, when connecting to the folder, I copied the path up to Layout, and this is the path I received (I made the path generic).  

https://companyname.sharepoint.com/ :x:/r/sites/SiteName/

In order to connect, I had to remove the :x:/r/ for the connection to be recognized.  

Hi Kent,

Have you tried copying the M code that Power BI creates into an Excel query?

I wonder if the version of Excel that you have doesn't support the connector you're trying to use.

Mynda

Thanks for the suggestion, and I tried it.  It still doesn't work.  I can connect to a SharePoint file in PowerBI, but when I try to connect to it using Power Query in Excel, I get the error message:  Unable to connect  We encountered an error while trying to connect.  Details: "Access to the resource is forbidden."

I copied the query from Advanced Editor in Power BI, and pasted it into the Advanced Editor in Excel Power Query, and it fails. 

When I executed the Query in Power BI, it asked for my credentials.  From Excel, it doesn't ask for credentials, it just fails. 

I am using the Web connector to connect to a file on SharePoint, I would think that would work.  I am running Office 365.  

How do I check my Power BI Desktop so I can compare accounts?  

The SharePoint connectors are only available with a Microsoft 365 Apps for Enterprise license. Please check the File tab under Account in Excel to see what license you have. If it's Home, Personal, Business or Business Premium then you don't have the SharePoint connector and this will be the problem.

That isn't it.  I have Microsoft Apps for enterprise. 

I am not sure why Excel doesn't ask for my credentials.  Any thoughts? 

By the way, I really enjoy your training videos.  I have looked several training courses, and yours is far and away the most informative, easy to understand, courses that I have found.  Thanks for the great courses.  

Hmmm, in that case all signs are pointing to a credentials issue i.e. the license you're logged into Excel with isn't compatible with the license that has access to the SharePoint data that you're using in Power BI.

Try going into the Power Query editor and opening the Data Source settings on the Home tab, then select the SharePoint connector and edit the credentials in there.

Mynda

P.S. Great to hear you're enjoying the course so far 🙂 thanks for your kind words!

You are a genius!  Power Query was trying to log me in as an Anonymous User.  Under Data Source Settings, I edited my permissions I changed from Anonymous to an Organizational Account, and it popped in, no problem.  Thanks so much for sticking with me and coming to a solution! 

By the way, days before Covid shut down the world, I was up diving the Great Barrier Reef, and touring other parts of Australia.  Had a great time!  

Great to hear it's fixed, Kent! 

Diving on the GBR is amazing. I haven't been for nearly 20 years, so probably time I go back 🙂