Automatic Data Refresh - How to do it?

[Chris-TX]

Hi Everyone!

BRAND NEW Power BI user!  I have what I think is a rather embarrassingly simple question I can't find the answer to (be gentle: I don't know what I'm doing, please speak to me like i'm 5 years old in explanations, I know nothing here!)

I have a Power Bi Desktop dashboard connected to an Excel sheet in a Sharepoint, which has then been published to the web for my manager to view, with the intention of and ultimately others in the organization (not there yet, maybe a week away).

Today, my manager updated budget information in the excel sheet, and expected to see that reflected on the web-published dashboard, which of course it wasn't.

As I currently understand it, the basic chain of command is as follows (which includes two manual refreshes):

1) Update excel sheet, hit save

2) hit REFRESH button on Power BI Desktop to get those visuals updated on the desktop, then

3) hit the PUBLISH button to get the updated information online.

All manual.  Is there a way to set it up so that if the excel sheet is updated, this refresh chain is automatic and doesn't require me to go in an manually refresh/publish everything?  Imagine me being out on vacation and my manager needing the updated visuals for internal stakeholders.

[mh2587 · Accepted solution]

You can achieve this by schedule refresh for please follow the link

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-scheduled-refresh

[bbbbi01]

Hi! Did you solve the probelm? I still don't quite understand. I'm using the desktop now. Is it possible to schedule a refresh using the desktop? Also the excel file has to be stored in the sharepoint? I'm a bit confused by their answers. Hope you can walk me through the process how you made it. Thank you!

[mwolfc]

I got so lost today!  About 4 hours.  Bringing a client on to Power BI because I told him how great it was.  Now, Microsoft has made this so much more complicated!  I reviewed all of the documentation, installed a "Gateway", never heard THAT one before.  And, nothing works!  After all of the options I tried, I see no options to auto-update anywhere!  It used to be that you just click an option in you PBI report!  Easy!  Microsoft documentation always has been written for software engineers only.  I would love to see precise, practical instructions written for this process.  Many software companies are making things easier, not harder.  It's not enough to know Power BI anymore, now we have to be computer engineers to make everything tie together!  Why?  

[AlexisOlson · Accepted solution]

The main purpose of Power BI Desktop is to build reports. The Power BI Service (online) is where reporting is viewed from.

As @mh2587 mentions, it's possible to set up a scheduled refresh cycle to keep the online reports up to date automatically. This scheduled refresh is like clicking refresh in the desktop app but updates the data stored online (but not your local PBIX file) and prevents you from having to republish whenever you want to update the data.

To bring the desktop file up to date, you click refresh in the PBIX or download a copy from the online service. There usually isn't much need to keep the PBIX always up to date since that file is generally used for building a report rather than viewing and sharing it.

[Chris-TX]

@AlexisOlson I note there is a refresh button on the top right.  Am I assuming correctly that this coudl immediately refresh everything outside of the scheduled refresh, IF my manager clicks this on his end?

[AlexisOlson]

Pages can be left open over multiple refresh periods. That button is there to make sure the visuals are reflecting the most recent data refresh (rather than whatever the data was when the page was initially opened) but doesn't kick off a new data set refresh.

There are other possibilities in addition to a scheduled refresh. DirectQuery is one approach but isn't compatible with an Excel source. One method I've used to automatically keep reports up to date with Excel sources is to store the Excel file in SharePoint and set up a data flow that triggers a dataset refresh in the Power BI service when the Excel file is modified (similar to the steps in this post but using a SharePoint file rather than a SharePoint list).

[Chris-TX]

@AlexisOlson Thank you!  This makes clear sense, really appreciate it! 

[mh2587 · Accepted solution]

You can achieve this by schedule refresh for please follow the link

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-scheduled-refresh

[Chris-TX]

@mh2587  Thank you for your reply!  That looks like its on the web, not at the Desktop level - essentially bypasses the desktop step.  By setting up a scheduled refresh on the web, will that update my desktop version as well the next time I open it?  

[Chris-TX]

Ah, definitely something I'm not aware of! Didn't know you could download the .pbix file from the web. Would simply hitting "refresh" in the desktop app accomplish the same thing as this download?

[mh2587]

No its not update your desktop file but you want to get updated desktop file you have to download it from online service after the scheduled refresh which will be updated  

[Chris-TX]

Thank you!  My apolgoies, but you've lost me here. Heres what I'm understanding from your response:

With scheduled refresh set up on the web,

1. any changes to the EXCEL sheet will in fact be visible on the web, but NOT my desktop app

2.  To update my desktop, you speak about "downloading it", which I don't understand.  Are you saying I need to do manual refresh within the desktop app when I'm ready? Or is there another functionality I'm not privy to that you may be speaking about?  Some sort of reverse update where the web-based actually updates my desktop?

[mh2587]

NO desktop will not update with the scheduled refresh of the online service

But 

      if you want to have updated pbix file(mean dataset) then you can download the pbix file from the web after the scheduled refresh and can open that file in desktop which will be updated

[Chris-TX]

Thank you so much!