cancel
Showing results for 
Search instead for 
Did you mean: 

Cloud Report Error "Invoked Custom Function references other queries"

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 

  • SCH1
  • SCH2
  • SCH3
  • And so on 

Query2 is set up as a custom function (simplified code with proprietary info altered/removed):

 

 

 

let QRYLOOKUP = (parSCHEMA as text) =>
let
Source = Sql.Database("SERVERNAME", "database", [Query="
SELECT
Amount,
ActionDate
FROM
["&parSCHEMA&"].tblInformation

"])
in
Source
in
QRYLOOKUP

 

 

 

 

Query1 then can invoke the custom function and expand the fields to return these results:

SchemaAmountActionDate
SCH1300.005/1/2022
SCH1450.005/2/2022
SCH2275.005/1/2022
SCH2180.005/3/2022
SCH3190.005/2/2022
SCH3255.005/4/2022
SCH4315.005/3/2022
SCH4410.005/4/2022

 

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:

 

 

 

ERROR:
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.

Status: Investigating

When combining two different data souces in Power Query, this error would happen due to the privacy limitation in Power BI.

 

In this issue, you can try that:

  1. After publishing this report to Power BI Service, configure a gateway for this report which should include all the data sources and set the same privacy level in gateway settings.
  2. Try to craete a dataflow in Power BI Service to replace the report and enable this feature in Power Query online when creating the dataflow:

vyingjl_1-1652669951691.png

 

Best Regards,
Community Support Team _ Yingjie Li

 

Comments
v-yingjl
Community Support
Status changed to: Investigating

When combining two different data souces in Power Query, this error would happen due to the privacy limitation in Power BI.

 

In this issue, you can try that:

  1. After publishing this report to Power BI Service, configure a gateway for this report which should include all the data sources and set the same privacy level in gateway settings.
  2. Try to craete a dataflow in Power BI Service to replace the report and enable this feature in Power Query online when creating the dataflow:

vyingjl_1-1652669951691.png

 

Best Regards,
Community Support Team _ Yingjie Li