Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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:
Schema | Amount | ActionDate |
SCH1 | 300.00 | 5/1/2022 |
SCH1 | 450.00 | 5/2/2022 |
SCH2 | 275.00 | 5/1/2022 |
SCH2 | 180.00 | 5/3/2022 |
SCH3 | 190.00 | 5/2/2022 |
SCH3 | 255.00 | 5/4/2022 |
SCH4 | 315.00 | 5/3/2022 |
SCH4 | 410.00 | 5/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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:
Best Regards,
Community Support Team _ Yingjie Li