Running a SQL query from another step in PowerBI Service

[dazzer360]

Good afternoon,

I am currently trying to create a report that has a step in as such:

Sql.Database("SERVERNAME", "DATABASENAME", [Query=Custom2])

Where Custom3 is a script from a previous step. We have gone through different scenarios but in this simple test case I have a SQL script in a Teams Excel Sheet that we pull in PowerBI and then try and run on our server.

However we get this error in Service when trying to refresh:

[Unable to combine data] Section1/SQL Run/Custom2 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. The exception was raised by the IDbCommand interface. Table: SQL Run.

The report can refresh in desktop but only with the option to Always ignore privacy level settings turned on.

Is there anyway around this or is it just not possible to run a script either directly obtained from another data source or dynamically generated from data from another data source? Following the steps from this (7 year old) blog post does not seem to solve the issue: 

Power Query Errors: Please Rebuild This Data CombinationThe Excelguru Blog

Are there any further ideas please?

Many thanks,
Darren

[v-eqin-msft · Accepted solution]

Hi @dazzer360 ,

This error can be caused by a number of reasons, such as the data privacy levels between data sources or the way that these data sources are being combined or merged. For more information about how to diagnose this issue, go to Data privacy firewall.

As you mentioned, the report can refresh in desktop but only with the option to Always ignore privacy level settings turned on. But to my knowledge, this will work locally, but not on the Power BI Web Service.

There are three workarounds provided in the blog:

1. Ignore Privacy Levels – You’ve probably read this one before, but you can just tick the box to ignore the privacy levels. This will work locally, but not on the Power BI Web Service.
2. Create a Power BI Custom Connector – this is BY FAR the best option as you can make sure that your queries can run in an optimal way and work seamlessly in the web service as well. Not to mention that you also have some features that are unique to custom connectors like being able to read the response headers of your calls and using setting your OAuth 2.0 flow. Sadly, this is only available for Power BI, but if you’re only going to work within Excel then the first option should be sufficient for your case
3. Embedding or masking your Data Sources inside Functions – this method will make your data sources not visible to the Data Privacy Levels at first, but you can tune your queries to define the data source at first and then apply a function.

In additional, you may try to change the privacy to Organization instead or put every stage used for the query into a single big query.

Similar threads:
Solved: Formula.Firewall: Query references other queries, ... - Microsoft Power BI Community

Solved: How to fetch data from middle of the another query - Microsoft Power BI Community

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

[dazzer360]

Hi Eyelyn, 

Thank you for your thorough response. 

In terms of the suggestion of "Create a Power BI Custom Connector" - having investigated this I believe this only works if we are using an On-Premise Data Gateway (as per here: Start developing custom connectors for Power Query - Power Query | Microsoft Docs). Our data sources are all in the cloud so we are not using a gateway. Am I right in thinking therefore this solution wouldnt be applicable?

Regarding the solution of 

"Embedding or masking your Data Sources inside Functions" -> Is there any examples around of how to do this do you know, please?

Kind regards,

Darren