I have several reports that are "invoking custom functions" to pull a list of information from one source and apply it to a second source. This has been a massive time-saver for our organization, allowing us to adjust massive numbers of reports by altering a single table.
As an example:
We have multiple tables spread across an ever-increasing number of schemas in our warehouse.
In Power Query/Power BI
Query1 runs a query against a table on our warehouse that contains a list of schemas
And so on
Query2 is set up as a custom function (simplified code with proprietary info altered/removed):
let QRYLOOKUP = (parSCHEMA as text) =>
Source = Sql.Database("SERVERNAME", "database", [Query="
Query1 then can invoke the custom function and expand the fields to return these results:
Whenever I try to perform this action it works fine on the desktop, but the moment I upload it to the Cloud, I receive the following error when I refresh my data:
Underlying error message: [Unable to combine data] Invoked Custom Function references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
I've rebuilt the queries a dozen different ways with no success. This is a recent change that appears to result from Microsoft screwing with the "partitions" for "security" and "privacy." Unfortunately, all it's done is break over 100 existing reports and force our lone developer, me, to have to manually rebuild all of the reports and their datasets, not only this time due to the breakage, but any time we add a new schema for our corporate structure.
Does anybody have a solution for this? This feature was one of the key reasons we opted to go with Power BI in the first place - dynamic queries.