Forum Discussion
Dynamic M query parameter binding to a query table which uses import mode
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
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
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!
Hi Sanjota05
In import mode, changing a parameter requires refreshing the model (which means refreshing the underlying data).I have a question: If we can import the data into the model, can we then use slicers to display the relevant data?
For example, if a user needs to view the products associated with Order ID 1234, can we simply set up a slicer based on the Order ID to display that specific data? Please let me know if I am misunderstanding anything here.
What is the core problem statement you are trying to solve?
ThanksHello Natarajan_M ,
You mean after loading the data in import mode instead of using parameter , I can use the associated orderID column directly in slicer and limit data ?
If my understanding of your question is correct, yes we can do that but in my case the data should be filtered from the query it self, not from the visual side. The data should filtered before loading
Because the data size is huge 40 lakhs of data is there so we are trying to limit the data before loading and users needs to have option load the data by different filtering values dynamically.
Problem statement:
I have already created Parameter and used in it custom sql in M query to limit data before loading. So now I need to make this parameters dynamic so that users can select any values and load the data from power query.
As my tables are import mode I cannot to create dynamic parameters using bind to parameter option which is available only in direct query.
Thank you!
