I'm working on multiple custom connectors that retrieve data from REST APIs. Over and over, I need to utilize the result of one query as input for another. For instance, I get a result/table of X things, and I need the ID of each item to query another API to get additional related data.
My assumption is that I could create a second shared function in my Custom Connector, utilize a navigation table to get the main tables of data that people are interested in, and then allow them to create another query manually to use the additional shared function, passing in a list from the table (basically using it as a referenced table).
However, my connector relies on OAuth and even though they are using a Custom Connector function, when it attempts to get the authentication context with Extension.CurrentCredential(), Power BI says "Expression.Error: We can only perform this operation while using a custom data connector."
This seems to imply that the Extention.CurrentCredential() can only be used during the initial invocation... even though all of them share the same "Datasource.Kind". If I modify the shared function to pass in the JWT directly (not utilizing the credential store), it works just fine. Obviously, this isn't a usable option for users.
From a query perspective (shown below), both queries have a source of the Custom Data Connector, so I'm confused why this wouldn't be allowed.
This query comes from the main navigation table function that can page in various GET requests selected from the table and work as expected, paging the api, etc.
Shared Function Definition in Custom Connector:
[DataSource.Kind="My_Connector", Publish="My_Connector.Publish"]
shared My_Connector.Contents = () => ApiNavTable(RootURL) as table;
Query in Power BI (produced from Nav Table selection):
let
Source = My_Connector.Contents(),
Corporations1 = Source{[Name="Corporations"]}[Data]
in
Corporations1My desire is to then use data from that table, manually in Power BI, to call a separate function that will use the current authentication, page the necessary API, and create another table.
This is the shared function definition for the second, non-NavTable function:
[Datasource.Kind="My_Connector"]
shared My_Connector.GetDetails = Value.ReplaceType(GetDetails, type function (Ids as any) as any);
And this is the query in Power BI:
let
Source = My_Connector.GetDetails({"00000000-0000-0000-0000-000000000001"})
in
SourceInternally both functions call Extention.CurrentCredential() which then triggers the needed auth flow if they are not authenticated. But as stated above, the second function throws an Expression.error on the credentials.
I've also tried to share out the function from the same Navigation Table, but when I then try to reference one table as the source of the data I need (the list of Ids), I get this error. Certainly it can't be this hard, can it??
Formula.Firewall: Query 'GetDetails' (step 'GetDetails1') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Any input on how to make a workflow like this possible for my users with a Custom Connector?
Much apprecaited!
Ryan
