One drive refresh not working

[VivianaTudora]

Hi everyone, 

I am looking into creating a report which has an automatic refresh and a source which can be edited by multiple users. 

I found out Onedrive would be suitable for this and it won't require a gateway, as far as i understand? (Still learning to use PowerBI)

I manged to connect PowerBI desktop to the Excel files by getting them from the Onedrive folder on my computer. However if I try to do get data, Web and use sharepoint, onedrive it tells me my credentials are not valid. I'm not sure what am I doing wrong ( I tried both basic sign in and my organizational log in) 

With the refresh part I didn't manage the have the onedrive date refresh hourly as many sites say. Where would I be able to access this feature? 

I can refresh manually on Dektop, but that won't refresh the PowerBI published report or the web publish. I am currently publishing on web and sending the report this way to all users as none of them have PowerBI. Is there any way to have the Published report on web also refresh once the Desktop App report is refreshed? 

Sorry for all the questions, hope this makes some sense! I'm just not sure which is the best way to go about setting up a scheduled refreshed report which can be seen by multiple users who don't have PowerBI. 

Thank you very much!

[v-yulgu-msft]

Hi @VivianaTudora,

---

I manged to connect PowerBI desktop to the Excel files by getting them from the Onedrive folder on my computer. However if I try to do get data, Web and use sharepoint, onedrive it tells me my credentials are not valid. 

---

You should choose SharePoint Folder data connector and enter the SharePoint site URL instead of the complete folder path, similar to:

https://microsoft.sharepoint.com/sitename/subsitename

---

With the refresh part I didn't manage the have the onedrive date refresh hourly as many sites say. Where would I be able to access this feature? 

---

Please upload your .pbix file to OneDrive or SharePoint online. Otherwise, please add data source to personal data gateway.

Reference: 

Refresh a dataset stored on OneDrive or SharePoint Online

Refresh a dataset created from an Excel workbook on OneDrive, or SharePoint Online

---

I am currently publishing on web and sending the report this way to all users as none of them have PowerBI. Is there any way to have the Published report on web also refresh once the Desktop App report is refreshed? 

---

After you publish report to web, data refreshes are automatically reflected in your embedded report or visual. Reference: Publish to web from Power BI

Best regards,

Yuliana Gu

[Anonymous]

Hi Yuliana,

We have a setup with a .PBIX file uploaded to a SharePoint Team site, where the OneDrive refresh feature is not working. The last refresh time stamp is shown from a few days ago.

How can we troubleshoot this?

Thank you in advance for your support.

JApolozan

[VivianaTudora]

Hi, Yuliana, thank you for the answer!

I've tried using the Sharepoint Folder a few times, unefortunately in never worked for me 😞 Maybe because it is a business level one, to get to my one I need to go a subsubsite, a third leve/ and even so if I get data it bring data related to sharepoint (files I posted, dates, name of those files. It doesn't let me to retrive the data within the file). I had the same issue with Get Data> Web, that's why I ended up using the Onedrive folder on my computer, it was the only option that was connecting and letting others contribute. Would you know what I could be dong wrong? 

I have posted my .pbix in the same folder in onedrive as the source Excel files, it didn't make any difference it refreshing. Works the same way. I wish I could install a gateway, but I don't have admin access to my computer (currently looking into that) However I've seen that gateways are recommended to be installed on a PC with wired internet connection and which is always on. I don't have an option like that. Would it be an issue to use my everyday laptop?

I wasn't aware it take 1 hour for the web refresh to work, I was always issuing a new link after refresh. I'll give that a try 🙂

Is web publish the usual way to go to share with people who don't have power BI? Or are there better options I'm not aware of? I did try the embed in Sharepoint link and it just looks like it's always loading. I'm not having much luck with Sharepoint 😞

[rixmcx59]

Would it be possible to store your data in an SPO list? you can use the "Import Spreadsheet" feature to create your list form Excel (make sure the excel data is a table) then you can follow my steps, connect to an SPO list, upload the .pbix, connect from PBI service, Publish to web then you insert a script editor web part on any SPO page and paste the code snippet from PBI service.

are you able to edit credentials in the data source porperties in Power BI service?

[VivianaTudora]

Hi again!

I've not used SPO before, but I don't have the option of using a table, the data will be replaced weekly and it's a lot of files to be transformed into tables.

I'm sure I'm doing some silly mistake just not sure what, 

Don't think so, I always change data sources in the Dektop app. Is there an option to do that in the online version?

What do you mean by credentials? I change different Excel files as data source for each report. 

[rixmcx59]

In order to schedule a data refresh in the Power BI service I have to sign in (just once) using my O365 organizational account (email).

Too bad you can't sell your users on SPO list, no more files and in data sheet view it behaves very similar to Excel and exports to Excel if needed. Our office had hundreds of Excel files flying around in emails collecting all kinds of data and I have all but eliminated them, we use SharePoint lists.

[rixmcx59]

I am not sure if this will help but it sounds like our situation has some similarities, here is what I did,

I created a report with a SharePoint Online list as a data source using PBI desktop, then uploaded the .pbix file to one drive.

Then I connect to that file from the PowerBI service, then use the Publish to Web feature to get the report code and embed that code in a SharePoint Online "Script Editor" web part.

Then you can set a scheduled refresh of your data or just do a manual refresh in the Power BI service. In you case you need to upload the .pbix and have the excel data source in your one drive. The refresh of the Power BI report uses the data connection path direct to the source, it does not go to the .pbix

hope this helps

[VivianaTudora]

Hi, @rixmcx59 thank you for the answer!

I've followed your steps, but did't have mutch success with the link to Sharepoint Online. I've got the link from the Online published report but it seems to never load once I try to open it. 

It does say on the Datasets pannel of the report in Power BI online that the next refresh is due tonight at midnight so that's a step forward. (it still doesn't let me change any settings on scheduled refresh)

However the manual refresh works the same as before. I can refresh Power BI desktop, re-publish to replace the dataset online, then re-issue the online link from the Online app to share with the others. No other refresh works without refreshing the .pbix

I'm not very hopeful the scheduled refresh would work any different at this point. 

Thank you very much for the advice!