Hello PBI Community,
I built a series of connected queries and merges pulling source data from REST API to publish to the service and then schedule to auto-refresh daily. The refresh was working on the desktop but not on the service. At first I fought with the Web.Contents problem with dynamic links, but after I resolved that the problem is now centering around the issue with multiple data sources not being allowed in the same query. The error message always being some permutation of
"[Unable to combine data] Section1/Get ToDivisionBinary/Filtered Hidden Files1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."
I reesearched this and implemented staging queries out of my single query in the style described in the Ken Puls post from 2015 (https://excelguru.ca/power-query-errors-please-rebuild-this-data-combination/), but that did not fully resolve the issue. The big problem is that I have to use the output from my 1st query in another web.contents call in my 2nd query and then I have to use a field from the 2nd query to make another 3rd web.contents call. So I cannot just run Query 1 and then Query 2 and then Query 3 without them being connected to each other, as each query seeds the next. Each of the calls results in a binary becoming available in the new column and a transformation query has to be run as well, not sure if that matters at all since these transformations do not refer to any new source.
I've started stripping all the pieces off to find out the bare workable query but even the basic combination of referencing the separate 1st query to utilize one of the inherited columns in a follow up web.contents call leaving out the transformation query afterward is resulting in the error in the service. What else can I do at this point?
Here is the critical stripped down query that is failing:
let
// Source references the end of a previous query which connected to am API datasource and whose output is needed in the RelativePath argument of the Web.Contents function
Source = #"GET Departments",
#"Removed Other Columns" = Table.SelectColumns(Source,{"Dept.externalCode", "URLRelativePath"}),
// Here is the Call Out to REST API
#"API Call toDivision" = Table.AddColumn(#"Removed Other Columns", "Custom", each Web.Contents("https://api4.successfactors.com:443/odata/v2", [ RelativePath = [URLRelativePath] , Query = [#"$format" = "JSON" ] ] )),
#"Filtered Hidden Files1" = Table.SelectRows(#"API Call toDivision", each [Attributes]?[Hidden]? <> true)
in
#"Filtered Hidden Files1"
Security for all sources is set to Organizational, Privacy is set to Ignore on the desktop. The error message only comes up in the service and is NOT preceded by the "Firewall" prefix that other posts mention.
Is there anything else that I can do?
Regards,
Henrik
