Can't schedule a refresh for a report using gateway data source and credentials data source

[Beej-NJ20]

I have a report that pulls data from a SQL DB source via a gateway and from Salesforce. In Power BI Desktop, it all works perfectly - refreshing both data sources on demand without error. When I publish the report to Power BI Service space, the service  wants to pull all data through the gateway and prevents me from making any changes to options. There are technically three data sources - but one is an MS Excel file that doesn't require any update. When I set that option in PowerBI Desktop, it is ignored by the service and the service keeps offering to add it to the gateway. 

So...

1) can a report have gateway and credentials AND non-refreshing data sources co-existing in the PowerBI Service?

2) can the report data be enabled for scheduled refreshes? If so, how?

Many thanks, in advance!

[ReportBuilder07]

Hi @Beej-NJ20 I've used the Salesforce connector too but I had some issues sometimes. As a workaround, maybe you can try to test your SF connection with a 3rd party connector. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:

After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:

There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor.  Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[kriscoupe]

Hi @Beej-NJ20,

1) Short answer is no. You can't have a non refreshing source in a dataset that is refreshing in the service. As you said you can do this in the desktop file by unchecking the "Include in report refresh" box in Power Query, but that only applies to Desktop.. rather annoying.

2) Yes! You have options. Here's a couple

- Move the Excel file to Sharepoint or OneDrive. You can then schedule the refresh for the SQL DB and Salesforce through gateway and as the Excel file is now in the cloud it doesn't need a gateway and can refresh. You'll need to repoint/redo your Power Query steps to now connect to the file in the cloud.

- If the Excel file is static and not very large you could use the enter data feature in Power BI desktop and copy/paste the file contents into the box that appears. This way you kind of embed the static data.

- You would have to add the server location or where the Excel file is stored as a data source on the gateway. For this you'd need gateway admin priviliges or know someone who does.

Hope the above helps!

Kris

[Beej-NJ20]

@kriscoupe Thanks for the quick reply! I thought that might be the issue. I had already started to convert the XL file into a PBI table using an Append query to grab the records from the original file. It's a date table being used to link all the data sources to a single slicer. I am working with my network admin to get OneDrive setup. I'll repost an update on my progress. Again - really appreciate the guidance!