Forum Discussion
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 clause to filter the data.
These parameters are created in power query and I am using it in slicer so that once I select other values the data should be loaded based on the parameter value selected. I am using SQL server.
I am not able to see bind to parameter in modelling for import mode, I can only see bind option to direct query.
1. Bind to parameter functionality will be enabled only for Direct query?
2. I have tried to load the data in direct query but when I create parameter and use it in SQL query in M query the storage mode is automatically import mode. Why the storage mode is choosen as import mode ?
Why there is no option to change storage mode to direct query?
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
26 Replies
Both your questions have the same root answer — this is by design, and understanding why will save a lot of frustration.
Q1 — Is "Bind to parameter" only for DirectQuery?
Yes, exactly. The "Bind to parameter" option in the Modeling tab only appears for DirectQuery tables. This is because the whole point of that feature is to pass a slicer selection as a parameter into a live query at query time. With Import mode, the data is already loaded into memory — there's no live query to inject the parameter into, so the binding mechanism doesn't apply.
Q2 — Why does the storage mode switch to Import when you use a parameter in the SQL query?
This is also by design, and it's actually a query folding / security consideration. When you embed an M parameter directly into a custom SQL query string (using string concatenation like "SELECT * FROM table WHERE col = '" & MyParam & "'"), Power BI detects that the query is dynamic and can't guarantee it can be safely folded or executed as a parameterized query in DirectQuery mode. So it falls back to Import.
What you actually need for your use case:
If you want slicer → parameter → filter data dynamically without a full refresh, you have two options:DirectQuery with native query parameters — Instead of embedding the parameter in a custom SQL string, use the connector's built-in parameter support. For SQL Server, use Value.NativeQuery with proper parameterization, or restructure so the parameter is applied as a filter step that Power BI can fold natively. Then "Bind to parameter" becomes available.
Import mode + scheduled refresh — Accept that with Import, the parameter value is fixed at refresh time. You'd set the parameter value, refresh, and the data reflects that choice. This works if your 9 queries don't need real-time slicer interactivity but rather pre-filtered datasets per deployment/environment.For true interactive slicer filtering against SQL Server without a full refresh trigger, DirectQuery with proper query folding is the path forward.
Hello Juan-Power-bi
I have this simple query
Let
Source=sql database("sever name","Database name"),
Sqltext="Select * from orders where orderID IN (@orderID) and cast(caseID as INT)>@pcaseIDstart and cast(caseID as INT )<@pcaseIDEnd and solutionstatus=1 "
Result=value nativequery(source,sqltext,[porderId = OrderID, pcaseIDstart= CASE_ID1, pcaseIDEnd=Caase_ID2])
I am already using value.nativequery with proper parameterisation. But the query automatically chooses import for security purposes.
1. If I want interactive slicer filtering against the sql server then my storage mode should be direct query only?
2. In my case the storage mode automatically chooses import mode due to using parameter in custom sql in M query so I only have an option to use non interactive slicer ? If the users needs to see data based on a different parameters value then developer needs to change the parameter value in power query and then load the data , then publish it.
Don't we have any alternative way to achieve this functionality in import mode itself?
3. Is it possible to load data using direct query it self by adding hardcoaded paramter values in SQL query and later once the data is loaded , can't we change the query to use parameters (@psolutionID) in advanced editor and load the data . Will this step keep storage mode as direct query only? So that I can use binding option in modelling?
I have tried the 3rd option but when I change the query in advanced editor and load I get an error saying this step can't be done in direct query mode and I get an option to switch all tables to import mode. --> needed your confirmation on this, is it expected behaviour?
Thank You!
If your query is currently in Import, Power BI loads data during refresh, so slicer selections cannot change the SQL that was already imported. Also, Power BI allows switching from DirectQuery to Import, but not the other way around.
Hi Sanjota05 ,
1) The bind to parameter is a direct query property that can't be leveraged in the Import modehttps://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
2) once the data loaded you can convert from import to Direct using the convenrional method modelling select table or using the find and replace option using tmdl
ThanksHello Natarajan_M
In model view changing the storage mode option disabled.
I converted the import mode to direct query using TMDL view but after converting 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
And I am also getting different type of errors for all the calculated columns.
My sql queries are using parameters----> Is this the reason for above unknown server and unknown database issue?
Thank you!
Hi Sanjota05 , Can you check your Power Query steps for calculated columns in direct query mode pbi enforces restrictions.
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about
Could you please share your Power Query?
Additionally, can you verify if it's possible to push the transformation into the source query?
ThanksIf this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way fasterThis is the M-query which I m using
Let
Source=sql database("sever name","Database name"),
Sqltext="Select * from orders where orderID IN (@orderID) and cast(caseID as INT)>@pcaseIDstart and cast(caseID as INT )<@pcaseIDEnd and solutionstatus=1 "
Result=value nativequery(source,sqltext,[porderId = OrderID, pcaseIDstart= CASE_ID1, pcaseIDEnd=Caase_ID2])
One of the error I am getting a is " unable to convert an M query in table "----" into a native source query"
Thank you!
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?
ThanksHello 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?
Thanks
Hi Sanjota05 , Thank you for reaching out to the Microsoft Community Forum.
In Power BI user driven filtering that happens before data is loaded only works with DirectQuery using dynamic M parameters. In Import mode, parameters are evaluated only at refresh time, so slicers cannot change them dynamically. That’s why you don’t see the Bind to parameter option, it simply isn’t supported in Import.
The reason you’re stuck in Import mode is your query design. Even though you used Value.NativeQuery, embedding parameters inside a custom SQL string with patterns like IN and CAST prevents Power BI from validating and folding the query into a native DirectQuery statement. When a query isn’t foldable or safe for DirectQuery, Power BI either forces Import or blocks conversion, which is exactly what you’re seeing with the errors and unknown source in TMDL.
If you need dynamic, slicer driven filtering before data is loaded, you must restructure the query to be foldable, avoid custom SQL strings and apply filters in M so Power BI can push them down and then use DirectQuery. If that redesign isn’t feasible, then you’ll have to stay in Import mode and handle this through pre-filtering and refresh, because dynamic parameters at runtime are not possible there.
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
DirectQuery in Power BI: When to Use, Limitations, Alternatives - Power BI | Microsoft Learn
Hello v-hashadapu ,
I have removed the custom sql , then loaded data and applied changes to modelling. The table is still in import mode even though I had removed the custom strings like IN and CAST. When I tried to change storage mode using TMDL, I got unknown server and database.
The redesigned query:
Sqltext="Select * from orders where orderID =@porderID and caseID >@pcaseIDstart and caseID<@pcaseIDEnd and solutionstatus=1 "
I need to stay in import mode and I need to pre filter data and refresh. Dynamic parameters are not possible in this case.
There are no other ways in this case?
Hi Sanjota05 ,
As long as you follow the parameter binding rules, you should be able to use the direct query successfully.
Please create a blank PBIX file, add the table, and check it.
I hope your model doesn’t have any RLS applied.
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
ThanksHello Natarajan_M
I have loaded the table in blank PBIX file and this time I just used only one parameter
Sqltext="Select * from orders where orderID =@porderID
Still import mode was choosen. Not sure what is the issue.
Thank you so much for your valuable assistance Natarajan_M
Hi Sanjota05 , Could you please share your exact Power Query, ensuring you mask your server details? I need to take a look at it.
ThanksHello Natarajan_M
Below I have provided you images of my query and parameter.
Instead of loading all columns I just tried loading only one column but still import mode.was choosen.
Thank you!
Hi Sanjota05 :
let Source = Sql.Database( "a", "b", [ Query = " SELECT * FROM x.y.z WHERE [Active] in (" & Number.ToText(@Active) &")" ] ) in Source
parameter :Thanks
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster
Hello Natarajan_M ,
I have added exactly same as your query but my query still choose import mode.
SolutionID is not a calculated columns, it is direct column form the table. I have added solutionID column details also.
Is there any settings do I need to enable?
Or anything else am I missing?
Hi Sanjota05 ,
First, create a blank query. Then, create a table without any parameters. When you apply and close it, you will be prompted to choose between "Import" or "Direct." Select "Direct."
If this step is successful, move on to the next step, which is to use the parameters and check them.
let Source = Sql.Database( "a", "b" ), Result = Value.NativeQuery( Source, "SELECT * from ABC", null, [EnableFolding = true] ) in Result
Thanks
Hi Sanjota05 , Thank you for reaching out to the Microsoft Community Forum.
We find the answer shared by Natarajan_M is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.
Thank you Natarajan_M for your valuable response.
