Refresh excel from OneDrive and Sql Database

[NerdFlanders]

Hey,

i've created a report with an excel file on onedrive as a data source and a sql database as another source (data import).

i was able to setup data refresh only when each source is the only one in a report, like a report with excel only.

But when i combine both sources in a report the Gateway will not be found and i can't setup a refresh.

Has anyone a solution how to setup a datarefresh on a report with these two kinds of data sources in one report?

Thank you.

[Anonymous]

Hi @NerdFlanders,

I test your scenario and encounter the same issue.

The issue is caused by that on-premises  gateway doesn’t work with online data sources, thus when you combine on-premises SQL Server data source and Onedrive, you are not able to use on-premises gateway to schedule refresh for the dataset. This is a known issue  that has been submitted in the following link and the feature that using on-premises gateway to work with online data sources is under review, please vote it.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13405644-combine-enterprise-gatew...

To work around the issue in your scenario, you can use the personal gateway to schedule refresh for your dataset.

Thanks,
Lydia Zhang

[NerdFlanders]

I couldn't make it work yet. We decided to just import the table from the excel to PowerBI via the "import Data" function, because the data from the excel will not change so often.

When i put the excel for example on a fileserver, how do i have to configure the gateway to refresh from this file server? And will it then work with the sql on-prem sql server?

There would be still two completely different data sources.

[Anonymous]

Hi @NerdFlanders,

When storing Excel on onedrive , I can use personal gateway to refresh the dataset. Have you tried personal gateway?

In addition, if you persist to use on-premises gateway, you would need to add Excel data source under the on-premises gateway after you move Excel to on-premises environment.

Thanks,
Lydia Zhang

[ankitpatira]

@NerdFlanders Most likely that issue is due to data source that is onedrive excel is connecting live to onedrive and other data source sql database is importing data. You cannot combine data sources of two different refresh mechanisms into one and have it refreshed. I would suggest you download excel file into local folder from onedrive (and have it regularly synced with onedrive) and then Import and also Import from sql database, then refresh in pbi service should work.