Hey all,
here's my solution to the problem in case someone still ends up reading this thread, struggling with the Firewall:
Processing error: | [Unable to combine data] Section1/MyQuery/Source references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. |
The solution is based on Behind the Scenes of the Data Privacy Firewall and has two parts to it:
- Put everything into a single Power Query M expression (Step).
Go to Power Query Editor, right click the query, select Advanced Editor, then make sure to re-write your Power Query M expressions to access all data sources in a single Step, without any references to other steps or queries. See the example below for more details. - Set Privacy Level to Organizational (or Public) for each data source.
From Power Query Editor, go to "File -> Options and settings -> Data source settings -> Data sources in current file", select each data source in turn and click on "Edit Permissions..." button, then select Organizational (or Public) for the Privacy Level and confirm with OK. Must have the same Privacy Level for each data source and it must be either Organizational or Public.
Here's an example to demonstrate the idea:
- First select a parameter value from one data source (simplified here to just select a constant)
- Then use the resulting value to build a query executed on another data source
First let's see the version that works well in Power BI Desktop, but does not work when published to a Power BI Service workspace:
let
Count = Number.ToText(Record.Field(Table.SingleRow(
Sql.Database("myserver1.database.windows.net", "mydb1",
[Query="select 10 as Count"])), "Count")),
Result = Sql.Database("myserver2.database.windows.net", "mydb2",
[Query="select top " & Count & " * from sys.objects order by create_date desc"])
in
Result
It does not matter here whether I had the Count as a separate query, or as a separate step. Either way, it ends up in a different "partition" and gets blocked by the Firewall.
Now let's see the version that works when published to a Power BI Service workspace:
let
Result = Sql.Database("myserver2.database.windows.net", "mydb2",
[Query="select top " &
Number.ToText(Record.Field(Table.SingleRow(
Sql.Database("myserver1.database.windows.net", "mydb1",
[Query="select 10 as Count"])), "Count"))
& " * from sys.objects order by create_date desc"])
in
Result
Now that everything is done in a single Step (named Result here), the Firewall will not complain as long as both data sources have the same Privacy Level (either Organizational or Public).
Hope this helps, cheers!
