cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dholloway Frequent Visitor
Frequent Visitor

Trying to get Power BI to work with an Excel Data Source

Hi all,

 

I'm scratching my head over this because I feel like it shouldn't be that difficult.  But it has been.

I've got a report that works fine on my computer and connects to Azure and to databases as well as an excel file.  When I upload this to the service, I can't get it to refresh because my gateway is just working for the databases, and not for the excel file.  I've tried a number of things from uploading this file to the one drive that was created by the group in office 365, and trying to access it there.  But i've been unsuccesful there too.

 

Ideally the file would be in onedrive and accessible to the business users who are in that group.  But when I try to connect power bi desktop, i get the message"unable to connect.  We encountered an error while trying connect.  Details: Access to resource is forbidden"

I'm using Get Data > Web and then pasting in the URL that is given for the one drive link.

 

Does anyone have any idea how to do this?  I've tried looking through the forum and at videos youtube and haven't really come up with much.

Thanks!

Drew

 

1 ACCEPTED SOLUTION

Accepted Solutions
dholloway Frequent Visitor
Frequent Visitor

Re: Trying to get Power BI to work with an Excel Data Source

Microsoft Support helped me find the answer.  It's actually pretty easy.  Just need to go to Edit Queries > Data Source Settings and you can get to all the remembered credentials. 

Why though would this be hidden under the Edit Queries button is beyond me.  It really needs to be displayed somewhere a little more obvious.  Or maybe shown as an option under Get Data as well...

 

But in here I can edit the settings and clear ones that I don't want applied. 

 

View solution in original post

9 REPLIES 9
blopez11 Established Member
Established Member

Re: Trying to get Power BI to work with an Excel Data Source

The following reference worked perfect for me

https://docs.microsoft.com/en-us/power-bi/desktop-use-onedrive-business-links

Good luck

Moderator v-yuezhe-msft
Moderator

Re: Trying to get Power BI to work with an Excel Data Source

@dholloway,

Does your dataset contain Azure SQL database data source and Excel file data source?  Or you combine Azure data source, on-premises SQL database and Excel file in a single dataset?


What error message do you get when adding Excel file within on-premises gateway? You can follow the guide in this similar thread to add Excel data source.


Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dholloway Frequent Visitor
Frequent Visitor

Re: Trying to get Power BI to work with an Excel Data Source

Thank you @blopez11 and @v-yuezhe-msft for the tips. 

The link that you provided @blopez11 proved helpful for getting me to try connecting again with a file from PBI Desktop.  I was able to successfully connect an Excel file to OneDrive using the in-depth walkthrough provided in this article: https://5minutebi.com/2018/04/09/power-bi-using-and-refreshing-excel-datasources-with-onedrive/

 

I was able to get it working with my personal OneDrive for Business account, but I really wanted it to work with the Sales Dash site that I'd set up, since this is what we're using for this project.  When I try to connect to the Sales Dash OneDrive using the process that works for my personal OneDrive account, I get the error:

 

Unable to Connect - OneDrive File.PNG

 

This error prevents me from moving any further.  Here is what OneDrive looks like for me.  I tried connecting from Sales Dashboard as well as from my Dev/Testing site, but I get the same error each time. 

 

OneDrive.PNG

 

 

 

Moderator v-yuezhe-msft
Moderator

Re: Trying to get Power BI to work with an Excel Data Source

@dholloway,

It seems that you are connecting to csv file that locates on SharePoint online site. Please enter the following url instead in web connector and use organizational account. There is an example for your reference.

https:// your root site url/Shared%20Documents/your folder name/yourcsvname.csv

0.PNG1.PNG

Regards.
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dholloway Frequent Visitor
Frequent Visitor

Re: Trying to get Power BI to work with an Excel Data Source

Thanks @v-yuezhe-msft (Lydia),

 

But unfortunately for me, the Access Web Content page (second screen shot that you sent) no longer comes up, so I don't have the choice for organizational account any longer.  I just get the error message Access to the resource is forbidden.  I'm thinking that there is a bug because I believe I entered the information in once, but I chose wrong on that Access Web Content page.  But now I can't get back to it. 

 

Anyways, I'm going to put the data into a database instead of an Excel file.  This should fix the issue for me, but it won't resolve the bug.

Thanks!

Drew

dholloway Frequent Visitor
Frequent Visitor

Re: Trying to get Power BI to work with an Excel Data Source

@v-yuezhe-msft (Lydia),

I believe I found the answer!

 

I was trying to load the Excel data by going through the process documented here:

https://docs.microsoft.com/en-us/power-bi/desktop-use-onedrive-business-links

 

But when I followed the steps I no longer see the Access Web Contents screen below:Access Web Contents.pngAccess Web contents screen

Instead I just go right to the error message:

Unable to Connect - OneDrive File.PNG

 

So that appears to be a bug in the Power BI program.  Admittedly, it's a pretty subtle one, but I was able to replicate it with another OneDrive for Business document that was made available through a SharePoint library.  It looks like I get the Acess Web Contents screen the first time, but then later, I just go right to the error message if my initial choice in Access Web Contents wasn't correct.

 

So here is the solution:

It's actually pretty easy.  Just go to the SharePoint / OneDrive folder contatining the files you want to work with and click Sync:

Solution - OneDrive File Sync.PNG

Then you will get a URL in file Explorer that you can use in Power BI.

This time in Power BI, click Get Data > Excel (instead of Web) and use the URL that you got from File Explorer.

Mine was C:\Users\username.DOMAIN\Company Name\Sales Dashboard - Documents.

Then I can get the data in Power BI.

 

 

dholloway Frequent Visitor
Frequent Visitor

Re: Trying to get Power BI to work with an Excel Data Source

Do this worked for getting the data into Power BI Desktop.  But it didn't solve my issue with Power BI getting to the service.  Instead I needed to move the data to the database.

😞

I've got a support ticket open with Microsoft for this issue.  I think it's a bug.

dholloway Frequent Visitor
Frequent Visitor

Re: Trying to get Power BI to work with an Excel Data Source

Microsoft Support helped me find the answer.  It's actually pretty easy.  Just need to go to Edit Queries > Data Source Settings and you can get to all the remembered credentials. 

Why though would this be hidden under the Edit Queries button is beyond me.  It really needs to be displayed somewhere a little more obvious.  Or maybe shown as an option under Get Data as well...

 

But in here I can edit the settings and clear ones that I don't want applied. 

 

View solution in original post

jessewysong Member
Member

Re: Trying to get Power BI to work with an Excel Data Source

THANK YOU for this. It must have saved some old 365 passwords to the sharepoint directory in question. The error message was worthless, a generic access denied warnging and no more. I was stumped for days. 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 245 members 2,611 guests
Please welcome our newest community members: