Data Refresh Fails When Report Merges Data Flow with Another Query Created in Desktop

[RossB]

I have created a report that contains two queries.  One is a dataflow and the other is a query that i created in desktop.

I merge the two queries and disable the load of the data flow from the model.

The report refreshes fine in desktop but fails in the service.

I have all my privacy settings to organisation in the gateway - i assume it is something to do with these settings

Can anyone suggest what i am doing wrong please?

[v-rzhou-msft]

Hi @RossB 

I followed your steps to have a test.

Firstly I get data from dataflow (Sheet1) and enter data (Table) directly into Power BI Desktop.

Then I merge two tables in Table and disable load Sheet1.

Then publish this report into Service.

Please check whether your gateway works normally.

If it works well, then check your Data source credentials in Setting - Datasets in Service. Click Edit credentials , select Authentication method and Privacy level. And sign in by your power bi account.

Then I refresh the dataset, it works well.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[RossB]

Hi @v-rzhou-msft  that you for the quick response, but i am still having trouble.  Perhaps i can clarify a few things

I am merging the data flow with a query that connects to an off premise sql database rather than data i have entered myself .  The error message is as follows

I have gone into the credentials and tried to update them however i get the following error message for the SQL connection

I am usually able to run reports from this SQL connection even though i have set skip test connections in the gateway. 

Next i tried to update my gateway connection by unchecking skip test connections, however....

when i uncheck skip test connection i get the following error

Could this be the cause of the issue?  If so, how do i resolve this, it is a network issue?

Thanks in advance

Ross

[v-rzhou-msft · Accepted solution]

Hi @RossB 

Please check whether your SQL Date Source setting is correct.  If you set it correctly, it may show connection successful.

Here are some blogs about refresh, I hope they could help you.

Blog: Troubleshooting refresh scenarios

Blog: Configure scheduled refresh

Blog: Data refresh in Power BI

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.