Data Gateway - SQL & SharePoint Excel File

[GeraldV]

Hi,

I am having issues with what I thought was going to be a simple report. I have two data sources, SQL server and an Excel sheet that is updated automatically using Zapier (Survey responses) and stored in OneDrive for business.

The OneDrive file is added using Get Data > From Web using the instructions found here :https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-onedrive-business-links/

My issue is when i add the file to the report and go to the Schedule refresh I get the error that I dont have a gateway installed or configured for the data sources in this dataset. If I remove the OneDrive file the SQL connector works fine.

Is there a way to schedule a refresh of two different datasets within one report?

Appreciate any help!

[v-yuezhe-msft · Accepted solution]

@GeraldV,

You combine on-premises data source(SQL Server) and online data source(OneDrive) in a single dataset, in your scenario, you can install Power BI on-premises gateway(personal mode) and use this gateway to refresh your dataset.

After you install and configure the gateway, go to Settings->Datasets and find your dataset in Power BI Service, enter credentials for the SQL data source and OneDrive data source, this way, you will be able to set schedule refresh for your dataset.

Regards,
Lydia