Forum Discussion
Running a SQL query from another step in PowerBI Service
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:
- 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.
- 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
- 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 CommunitySolved: 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.
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:
- 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.
- 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
- 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.
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
