Using imported Excel files and direct query to SQL on same report.

[Alex_Stennett]

Hi.  I am trying to update an existing, working, Power BI report based on Excel files stored on prem.  Because of the nature of the report this can be downloaded, data refreshed then republished when needed (monthly).  The issue comes when we want to add some direct query connections to Azure SQL via a data gateway.  This works fine in Power BI Desktop as all of the data sources are accessible but fails once published.  It needs to set the gateway on the connection but can't as it is also looking for the Excel files in the same gateway connection.  It would not be a simple thing to add these Excel files to the gateway (as far as I know).

I have tried creating a second report with just the SQL connections on it then referring the main report to the dataset for this new report.  Again it works in PBI Desktop but fails when published.

Is there a way of combining these data sources in one report or do I just need to keep the two spearate?

Thanks in advance

Alex

[v-yinliw-msft]

Hi @Alex_Stennett ,

Could you please tell me the details of the error?

Whether this data source is the first or the second, it cannot avoid the need for a gateway and credentials if you want to refresh the reports.

If the error occured when you published, you can check this button turn on or off.

I think you can try to configure the gateway and credentials for the SQL data source.

Best Regards,

Community Support Team _Yinliw

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

[Alex_Stennett]

I think that is most of my problem.  This report was designed by someone else and was never meant to refresh whilst on the PBI Server.  What they did was download the PBIX file, refresh data locally and re-publish.  Now when I try and add "Live" data it puts them all into the dataset for the report and tries to refresh.  It would be able to reach SQL (and can in its own report) but cannot reach the Excel files so the whole refresh fails.  What I was hoping for was for two datasets, one that could refreesh (direct query really) and one that didn't (Excel files updated monthly).

I'll have a look for that setting which I am assuming is a PBI admin type thing?

Thanks