Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi PowerBi fans.
hope you can help me out. See picture below.
Trying to call aan WebAPI. Works fine without parameters.
Using a parameter as a part of the URL works fine in desktop mode.
Problems start when trying to refresh the data in the PowerBi service enviroment.
Error: " [Unable to combine data] Section1/Verwachting 16 dagen/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."
When removing the paramater from the URL ==> refresh works fine.
Any help or hint will be greatly appreciated.
Best regards,
Wouter
Solved! Go to Solution.
SOLVED - Workarround.
The issue was not using the parameter. The issue was that the parameter had a reference to a query (Suggested values). Changed the suggested values to 'any value'. Now refresh in the powerbu service works perfectly.
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:
Here's an example to demonstrate the idea:
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!
Hi Wouter
I'm getting the same error message that you solved, where I'm using a WebAPI with a changing parameter as a data source to combine data. It works fine in desktop, but when trying to refresh the Power BI model in service it fails with the message:
[Unable to combine data] Section1/CombinedVisitorQuery/Added Custom2 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Table: MergedVisitors.
On your solution:
"The issue was not using the parameter. The issue was that the parameter had a reference to a query (Suggested values). Changed the suggested values to 'any value'. Now refresh in the powerbu service works perfectly."
Where do you change the suggested values to 'any value'? I'm not following this logic. Do you perhaps have a screenshot of what you're doing here?
Thanks in advance.
Laurie
Hi Laurie,
The option is found when editing in Power BI Desktop.
Navigate to 'Edit Queries', locate your parameter, double click to enter the Manage Parameter mode.
Directly above where your parameter value has been entered, there is an option that is set as "List of Values" or "Query"
simply click the drop down and select "Any Value".
Obviously you will then need to replublish
I've just done this and it has solved my issues.
Arthur B Chalk - eXPD8 Analytics
Hi guys !
I'm getting the same error message like Laurie. The message refers to a table.
Message[Unable to combine data] Section1/GroupesDActivites/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Table: ActivityGroups.
In this specific table, I selected some columns, extand some of them to get fields from others table, merge two columns and add customs columns with concatenated values...
I can refresh my report correctly on desktop but not online where I get this error.
I don't use any parameters then I cannot use the Wouter's workaround.
Do you think doing all these steps could be the root cause ?
Thanks,
Damien
Hi all,
I have the exact same error (only on the Power BI Service). My parameter is already set to Any. Out of a query, I'm creating a "Staging" second query where I keep only a list of values that would be the values of my parameter. In 3rd queries I call this list and after that I invoke the function.
I tried every possible combination of using this list of values to combine it with the funcion query, but nothing worked.
I even set "Ignore Privacy level settings", but it didn't help.
Any suggestions would be very helpful!
TIA
Hi
There can be multiple reasons for this, but they mainly relate to the Firewall issue describe by Chris Webb here https://blog.crossjoin.co.uk/2019/06/18/detailed-article-on-the-power-query-formula-firewall-and-dat... have a read and see if it applies (from what you write I think it does).
One 'workaround' that I've successfully done myself is to write your own custom Power BI data connector. Doing this will allow you to maintain the Power Query logic you've already created. Alternatively you need to be able to call the API in one go, but I know that is not always an option, especially in your case (which is similar to what I had).
Write it as a custom connector treats the entire connection as one and applies the privacy setting to that eventhough you call the the same or different API endpoints in your custom logic.
Hope this helps, it should also apply to your issue @mariopavic
There is obviously more of us with the same issue. Maybe you can post your analysis on this thread (maybe we'll find the solution together) https://community.powerbi.com/t5/Desktop/Scheduled-refresh-not-working-because-of-error-quot-Unable-...
SOLVED - Workarround.
The issue was not using the parameter. The issue was that the parameter had a reference to a query (Suggested values). Changed the suggested values to 'any value'. Now refresh in the powerbu service works perfectly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.