Refresh Excel without refreshing through connections

[fredrikg]

Hi,

The subject might sound strange, but here is what I am trying to do:

I have an Excel document with a data model that I load from SQL Server, on-premise. Our IT-department won't let me install any Personal Gateway, but that is ok (I thought). The underlying data only refreshes once per month, and I can update manually.

Here is what I tried to do.

1. Load data into the data model of the Excel document, from SQL Server.

1. Store the Excel document on OneDrive

2. Get Data->Files->OneDrive for business->Import Excel Data into Power BI

3. Build a report on the dataset

Up to now, everything works fine.

But, a month passes, and I need to refresh the data in the document. So, I do this:

1. Open the Excel document from OneDrive

2. Refresh data data model (i.e. loading the data from SQL Server again). Not this is entirely on-premise

3. Save the Excel document. At this point, the data in the document on OneDrive is up-to-date. All I need to do is have Power BI use this updated date.

4. Perform a "Refresh Now" on the dataset.

This gives "Your Personal Gateway is offline or could not be reached."

I guess that this is because Power BI will try to refresh the data model, using a personal gateway. And since I haven't installed one, this fails.

But, I don't want Power BI to refresh the data model, I just want it do use the new data that I just saved in the Excel document on OneDrive.

Is this scenario not supported, or am I missing something? Hope I have made myself understood.

[Youssef · Accepted solution]

Hi @fredrikg,

Wondering how this worked for you? @ashishrj's solution should work. You can also keep using Excel 2013, and Power BI will pick up the changes made on the OneDrive file automatically without needing to click 'refresh now'. All files uploaded from both OneDrive for Business and personal OneDrive are kept up to date with the OneDrive version.

When you clicked Refresh Now, Power BI attempted to refresh the actual connections (which are to your on prem database), that;s why it showed you that error since it needs the gateway to access that source.

Hope this helps. Let us know if you are still facing issues.

[ashishrj]

@fredrikg You can try moving data in Excel 2016 and upload your file on One Drive for Business and you can see that you can directly publish file to Power BI Online and without the need of Gateway to refresh. Simply keep updating your Excel file updated and rest will do your work. I have tried this and works smooth. For more info on this read blog provided here.

Hope this helps!

[fredrikg]

Thanks, ashishrj.

Do you know if this works even if my Excel workbook's data model has data connections? That is what causing my problem - if I upload a workbook with the data stored in the workbook itself (and linked into the data model), refresh works fine, even with Excel 2013.

Just want to check before I ask my boss to get me a license of Office 2016 🙂

/Fredrik