Nested API call from Direct Query table column

[Anonymous]

Hello!

I have got the following scenario:

• Azure SQL Server Database connected with Direct Query 
• Table name: Sales
• Web API that returns a JSON file, and that should only be triggered when a user requets data.
• The API queries a Data Lake that contains Raw Data. It is too much information to be stored in another database, so we need that in execution time. Also, it is not feasible to load it to the model.

I tried to create a nested API call from a column of the "Sales" table using the following code in the Query Editor:

=Table.AddColumn(#"context", "test column", each Json.Document(Web.Contents("https: //webapiurl?$filter=client_id eq " & [client_id], [Headers=[accept="application/json"]])))

After adding this step, the result was just fine, but the following message appears in the query editor:

"This step results in a query that is not supported in DirectQuery mode." [Switch all tables to Import mode]

When trying to apply the query changes, I get the following error:

"Vehicle

This query contains transformations that can't be used for DirectQuery."

I also tried to use the OData.Feed() function instead of using Json.Document(), and also tried to create a Power Query function to call the API, but the result is still the same.

I thought that the composite models functionality would enable me to do that, but it looks like it is not possible.

Any ideas on how to accomplish this goal without changing the model to Import mode?

Thank you all in advance!

[v-shex-msft]

Hi @Anonymous ,

In fact, this notice not related to your connector. When you works with directquery mode, power bi will limit advanced operations in query edit.

You can extract 'client id' list as new query and do invoke operation on new query table with composite mode.

Use composite models in Power BI Desktop
Regards,

Xiaoxin Sheng

[Anonymous]

---

@v-shex-msft wrote:

Hi @Anonymous ,

In fact, this notice not related to your connector. When you works with directquery mode, power bi will limit advanced operations in query edit.

You can extract 'client id' list as new query and do invoke operation on new query table with composite mode.

Use composite models in Power BI Desktop
Regards,

Xiaoxin Sheng

---

Hi @v-shex-msft ,

Thanks for your reply!

The issue to me is importing the raw data from the API. I need that to be loaded to the model only when a user tries to see data from a specific client. And that data should be removed after certain time. I was thinking about combining Power BI and Logic Apps, but could not come up with a solution yet.

Best regards!

[v-shex-msft · Accepted solution]

HI @Anonymous ,

>>I need that to be loaded to the model only when a user tries to see data from a specific client. 

It is impossible to achieve dynamic data import based on report view options, you can use query parameter to design parametrized query but it still need to manually change parameters.

Deep Dive into Query Parameters and Power BI Templates

In my opinion, I'd like to suggest you to import whole records and enable RLS based on current username to filter unmatched records.

RLS with UserName()

Regards,

Xiaoxin Sheng