Forum Discussion

Sanjota05's avatar
Sanjota05
Frequent Visitor
5 months ago
Solved

Dynamic M query parameter binding to a query table which uses import mode

At the moment I am using a dynamic M queries within a SQL queries which is in import mode. I have 9 custom sql queries where the data is being filtered in these queries by adding parameters in where ...
Need Help
Show and Tell
Tips and Tricks
  • Natarajan_M's avatar
    Natarajan_M
    5 months ago

    Hi Sanjota07 ,


    Non-null assertion failure: resource: Resource should be available for query Query2 to store FoldedArtifacts in cache This corresponds to that the pbi engine cant fold the entire query in direct query mode so this flag is raised 

    You can also try this approach :
    let
    Source = Sql.Database("",""),
    PrCase = Source{[Schema = "dbo", Item = "Table"]}[Data],
    FilteredRows = Table.SelectRows(PrCase, each [Active] = #"Active")
    in
    FilteredRows

    Thanks

Natarajan_M's avatar
Natarajan_M
Super User
5 months ago

Hi Sanjota05  , Could you remove the IN (@orderID) condition from your query and check the query execution? Also, could you share how the @orderID parameter is configured?

Thanks 

Sanjota05's avatar
Sanjota05
Frequent Visitor
5 months ago

Hello Natarajan_M ,

 

If the IN (@pOrderID) condition is removed from the query, how can the OrderID filter be applied instead?

 

OrderID param is configured as

Type: decimal number

Suggested value : any

Current value: 1234

 

Note: the M query is executed without any issue in power query.

 

When I tried to convert import to directquery in TMDL I started facing the below issue

If I hover on the tables in TMDL view, I can see this info for these sections

data source type: we could not detect the data source information for this table

service: unknown

database: unknown 

 

One of the error I am getting a is " unable to convert an M query in table "----" into a native source query"

 

 

Brief of my issue:

 

I have already loaded data using M query. Parameter is used in M query in order to filter data. 

I need to make this parameters dynamic so that users can select different parameters values and load the data. The tables are using import mode, due to which I can't use bind to parameter functionality. My query uses import mode automatically due to uasage of params in SQL, imporrt mode is used automatically for security. purposes. But I can't convert the import mode to direct query in modelling .

 

1.Is it possible to make parameters dynamic in Import mode using any alternative approach?

 

2.Since my query uses parameters in a custom SQL query, I am unable to convert the model from Import mode to DirectQuery. Does using parameters in a custom SQL query always force the dataset to load in Import mode?

 

3.If users want to fetch data based on different parameter values, does the developer need to change the parameter value and republish the dashboard each time when using Import mode? Or is there any better approach to handle this scenario?

 

Thank you!