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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.