Help resolving Access Denied error when importing query built in exel, saved in sharepoint, into BI.

[Victoria-NHA]

Hi everyone, I'm trying to import a query I built out in Excel into Power BI. I'm receiving the error message in the picture above and haven't been able to resolve why I'm getting the error.

I read this document ( @https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-import-excel-workbooks#are-there-any...) to help me with importing the query into Power BI. I know that I can use the file explorer to access OneDrive and import the book that way. My concern with this method though is if I were to get a new computer or leave the organization that the query would break since my OneDrive file path would no longer exist (or it would be altered) <--- Is this a legitimate concern or am I misunderstanding something here with using the file explorer to access SharePoint?

So I'm instead wanting to grab the URL for the query and paste that into the import action in Power BI and I'm receiving the error message above. From some google searching, it seems like this would be a possible method to help figure this out ( @https://learn.microsoft.com/en-us/sharepoint/manage-sites-in-new-admin-center).

However, I already have admin privileges and this should be able to work.

Can you help me troubleshoot this?!

[v-jingzhan-msft · Accepted solution]

Hi @Victoria-NHA 

It seems that you are trying to use the following feature for an excel workbook stored in SharePoint. 

In Power BI Desktop this feature launches the Windows File Explorer to find an Excel file. The problem is that the Windows File Explorer seems to only find files from the local drive/network folder/local OndDrive folder. When pasting the file URL in the File Name input box, I can reproduce the same error as the original post showed. I'm not sure if it can access a sharepoint folder through the File Explorer. I made many searches for this but found nothing helpful. 

Since the action of this feature is a one-time event. Once created with these steps, the Power BI Desktop file has no dependence on the original Excel workbook. If your purpose is to store the Excel workbooks for further importing, you can store them in SharePoint. Next time when you want to import the queries, you just need to download the Excel file to the local drive and then import. 

I hope this would be helpful. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[blackanese27]

Thanks a lot for the help with this. It's very much appreciated!

[christinepayton]

Here's a tutorial on my preferred method to reference files from SharePoint - basically you put them in a shared team site, then swap out the path in the query from desktop to SharePoint. The tricky bit is getting the correct path for the file, since the normal "copy link" won't be the right one:

https://www.youtube.com/watch?v=LYu3wqb2Nx4

[Victoria-NHA]

Hi @christinepayton thank you for your response ---- it appears that the video you provided is set to private and inaccessible. Are you able to send a non-priviate link along? Thanks!

[christinepayton]

Whoops must have copied the wrong one 🙃 - try this? https://www.youtube.com/watch?v=LYu3wqb2Nx4 

Also the error message you're getting makes me think you're selecting the incorrect authentication type when you enter credentials - make sure to select Microsoft account, not anonymous-- 

[blackanese27]

Hi Christine, thanks for the help on this! For the credentials, if I do have the Microsoft account selected what else should I try and checkout? Thanks! 

[christinepayton]

There's not a lot of info to go on about what you're currently doing - if it's working in Power Query in Excel, you should just be able to copy the advanced query text and paste it in a new blank query in Power BI and it should work. Make sure you're logged in with the same account. If you're doing the connect-locally-then-swap-paths technique from the video, just make sure you have the right link to the file. Presumably if you have permission enough to navigate to the place to copy the link, you should have enough permission to connect to it in Power BI. 🤨

I'd take whatever URL you're using in the connection and paste it in a browser to make sure it's right/that you can access it, too-- 

[lbendlin]

Loading Excel files into a workspace is generally frowned upon.  Put the files on a (team) OneDrive and access them from there.

[blackanese27]

Can you elaborate on why it's frowned upon? 

[lbendlin]

It's a bit of a black hole.  You have to take Microsoft by their word that they will refresh the semantic model "within the hour"  of changes in the file. You are also abusing the workspace as a data store.  There are no guarantees whatsoever that this is a safe or reliable place to store data.