Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Nested API call from Direct Query table column

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!

1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable


@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!

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.