Error in Gateway if I merge dataflow and SQL Server data in Power Query (M)

[Anonymous]

I am unable to schedule refresh and getting error in Gateway if I merge dataflow and SQL Server data in Power Query.

(M).

[v-qiuyu-msft]

Hi @Anonymous,

Which data source in does data flow get data from? If the data source requires gateway, please ensure the dataflow and SQL data source use the same data gateway, then test it again. 

Also please ensure you are running the latest on-premises data gateway version. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft,

There is no gateway for dataflows right. I tried adding it to gateways and there is no option. I configured gateway for other datasource which is on-prem SQL Server.

[v-qiuyu-msft]

Hi @Anonymous,

Please go to the Manage Gateways section, find the gateway which you contains the SQL data source used in Power BI desktop, ensure this option is enabled: 

You needn't to add the dataflow to the gateway. If you have above option enabled, after publishing the report, go to Dataset Settings, the gateway should be available. You can select the gateway and enter credential for the PowerBI data source. 

Best Regards,
Qiuyun Yu 

[asmith]

@v-qiuyu-msft Thanks for the detailed reply to the OP. I have what might be called 'part 2' of the above issue so it is probably best to continue here (I hope).

In my pbix file, I have SQL on-prem data combined with a Dataflow dataset. The Dataflow updates daily without fail; it is sourced with CSV files via the Sharepoint folder connector. Anyway, I can refresh the pbix dataset via PBI Desktop and upload to the Power BI service. Most importantly, I can schedule an automatic refresh once the dataset is in the App Workspace in the Power BI service. In other words, I see my gateway as you indicated above and it appears like everything (gateway connection, data source credentials, scheduled refresh settings) should work properly.

However, during each scheduled refresh I get the following error message:

'The provider is not support or due to invalid data source. Please check the data source connection string.

Cluster URI: WABI-WEST-US-redirect.analysis.windows.net'

Is this expected behaviour as the Dataflows capability rolls out? Or is there a solution? If not, I'll add this as an idea, for sure. 

[v-qiuyu-msft]

Hi @asmith,

This is different from this topic. Please create a new thread, clarify if it's on-premises SharePoint or online. Also provide other detail refresh error information, eg: RequestID and Time. 

Best Regards,
Qiuyun Yu