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

[Sanjota05]

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?

[v-hashadapu]

Hi @Sanjota05 , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.

[v-hashadapu]

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.

[Natarajan_M]

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

 

[Sanjota05]

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?

[Natarajan_M]

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 

[Sanjota05]

Hello @Natarajan_M 

I have followed your steps and I got option to select the storage mode, after selecting storage mode I am getting an error once I cancel the error the data gets loaded without any issue and the storage mode is also direct query only but when I hover over the table I can see unknown server and unknown database.

[Natarajan_M]

Can you click on the copy details to clipboard and share the details @Sanjota05 ? 

I think the error is related to query folding ,can you check if your query is getting folded or not ?

Thanks

[Sanjota07]

Hi @Natarajan_M 

Feedback Type:
Frown (Error)

Timestamp:
2026-03-19T06:15:53.6721040Z

Local Time:
2026-03-19T06:15:53.6721040+00:00

Session ID:
eabb1f69-96f5-464d-9b7b-9f37cb016647

Release:
August 2025

Product Version:
2.146.1254.0 (25.08)+791a7518fadc83da883d79322a2658aff57c8db3 (x64)

Error Message:
Non-null assertion failure: resource: Resource should be available for query Query2 to store FoldedArtifacts in cache

Stack Trace:
System.ArgumentException
at Microsoft.PowerBI.Modeling.Common.Contract.FailArgumentCore(String msg)
at Microsoft.PowerBI.Client.Windows.QueryFolding.FoldedArtifactsGenerator.GenerateAndAddArtifactsForEvaluatedQueries(IReadOnlyDictionary`2 queriesAndFoldedArtifacts, IDataModel model, IReadOnlyDictionary`2 queryDependencyGraph, IReadOnlyDictionary`2 queryResourceMap)
at Microsoft.PowerBI.Client.Windows.Modeling.Hosting.DesktopModelingHost.<LoadToModel>d__17.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.<>c__DisplayClass44_0.<<StartLoadToReportFlow>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.Mashup.Client.UI.Shared.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__1.MoveNext()

Stack Trace Message:
Non-null assertion failure: resource: Resource should be available for query Query2 to store FoldedArtifacts in cache

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.PowerBI.Client.PowerBIUnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Client.UI.Shared.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__1.MoveNext()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()
at System.Threading.Tasks.AwaitTaskContinuation.RunCallback(ContextCallback callback, Object state, Task& currentTask)
at System.Threading.Tasks.Task.FinishContinuations()
at System.Threading.Tasks.Task.Finish(Boolean bUserDelegateExecuted)
at System.Threading.Tasks.Task`1.TrySetException(Object exceptionObject)
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.SetException(Exception exception)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.<>c__DisplayClass44_0.<<StartLoadToReportFlow>b__0>d.MoveNext()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()
at System.Threading.Tasks.AwaitTaskContinuation.RunCallback(ContextCallback callback, Object state, Task& currentTask)
at System.Threading.Tasks.Task.FinishContinuations()
at System.Threading.Tasks.Task.Finish(Boolean bUserDelegateExecuted)
at System.Threading.Tasks.Task`1.TrySetException(Object exceptionObject)
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.SetException(Exception exception)
at Microsoft.PowerBI.Client.Windows.Modeling.Hosting.DesktopModelingHost.<LoadToModel>d__17.MoveNext()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()
at System.Threading.Tasks.AwaitTaskContinuation.RunCallback(ContextCallback callback, Object state, Task& currentTask)
at System.Threading.Tasks.Task.FinishContinuations()
at System.Threading.Tasks.Task`1.TrySetResult(TResult result)
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.SetResult(TResult result)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.<ContinueWithAsyncOperation>d__13.MoveNext()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Windows.DialogWindowBase.ShowModal(IWindowHandle windowHandle)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Windows.Services.PowerBIWindowService.<>c__DisplayClass10_0.<Microsoft.Mashup.Client.UI.Shared.Ux.IWindowService.ShowDialog>b__0()
at Microsoft.PowerBI.Client.Windows.Services.PowerBIWindowService.Trace[T](String action, String title, String details, Func`1 showDialog, Func`2 getResult)
at Microsoft.PowerBI.Client.Windows.Services.PowerBIWindowService.Trace(String action, String title, String details, Func`1 showDialog)
at Microsoft.PowerBI.Client.Windows.Services.PowerBIWindowService.Microsoft.Mashup.Client.UI.Shared.Ux.IWindowService.ShowDialog(IDialog dialog)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.<>c__DisplayClass20_0.<ShowDialog>b__0()
at Microsoft.PowerBI.Client.Windows.Services.TelemetryHelper.RunInActivity[T](IPowerBITelemetryService telemetryService, ITelemetryEvent telemetryEvent, Func`1 action)
at Microsoft.PowerBI.Client.Windows.Services.LoadToReportDialogManager.Show(ILegacyUIHost uiHost, IExceptionHandler exceptionHandler, IPowerBIWindowService windowService, IPowerBIQueryErrorEditor queryErrorEditor, LoadToModelContext loadToModelContext, LocalizedString title)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.<>c__DisplayClass19_0.<TryShowDialogForQueries>b__0()
at Microsoft.PowerBI.Client.Windows.Report.<>c__DisplayClass82_0`1.<SynchronizeLooselyWithQueries>b__0()
at Microsoft.PowerBI.Client.Windows.Report.<SynchronizeLooselyWithQueriesAsync>d__83`1.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.Report.SynchronizeLooselyWithQueriesAsync[T](Func`1 getTask)
at Microsoft.PowerBI.Client.Windows.Report.SynchronizeLooselyWithQueries[T](Func`1 action)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.TryShowDialogForQueries(Report report, IExceptionHandler exceptionHandler, IPowerBIWindowService windowService, IPowerBIQueryErrorEditor queryErrorEditor, IEnumerable`1 queriesToApply, String loadReason, ITelemetryService modelingTelemetryService, QueryLoadStages queryLoadStages, LocalizedString title, Action`1 beforeSchemaSyncCallback, Action`1 afterSchemaSyncCallback, Boolean synchronizeLooselyWithQueries)
at Microsoft.PowerBI.Client.Windows.Services.LegacyMashupEditorService.ApplyQueryChanges(IEnumerable`1 queriesToLoad, ITelemetryService telemetryService)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.AppModule.<>c__DisplayClass3_0.<Run>b__0()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.AppModule.Run()
at Microsoft.PowerBI.Client.Program.RunApplicationFlow(String[] args, IPowerBIRootTrace trace)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.146.1254.0","ModuleName":"Microsoft.PowerBI.Modeling.Common.dll","Component":"Microsoft.PowerBI.Modeling.Common.Contract","Error":"System.ArgumentException","MethodDef":"FailArgumentCore","ErrorOffset":"28","ErrorCode":""}

OS Version:
Microsoft Windows NT 10.0.17763.0 (x64 en-US)

CLR Version:
4.7.2 or later [Release Number = 461814]

Peak Virtual Memory:
86.8 GB

Private Memory:
545 MB

Peak Working Set:
740 MB

IE Version:
11.1790.17763.0

User ID:
26c8085f-d7f8-4103-80af-1449c307da40

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Snapshot Trace Logs:
C:\Users\xpPBIMigInfosys_f25\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShotfa82b1b4-9370-42e3-90ba-303191951c4f.zip

Model Default Mode:
Composite

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\xpPBIMigInfosys_f25\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Enabled Preview Features:
PBI_googleBigQueryAdbcVersionEnabled
PBI_shapeMapVisualEnabled
PBI_enhancedTooltips
PBI_scorecardVisual
PBI_setLabelOnExportPdf
PBI_oneDriveSave
PBI_oneDriveShare
PBI_odspSaveBackgroundUpload
PBI_modernOfficeFilePicker
PBI_useModernPublishDialogs
PBI_gitIntegration
PBI_tmdlInDataset
PBI_directLakeRemoteModeling
PBI_directLakeOnOneLake
PBI_newCard
PBI_buttonSlicerAuthoring
PBI_advancedSlicerTypeList
PBI_textSlicer
PBI_daxInlineCopilot
PBI_aiNarrativesVisual
PBI_visualCalculationsAuthoring
PBI_tmdlViewToggle
PBI_copilotUnifiedTooling
PBI_UserInstalledVerticaODBCDriver
PBI_sqlDbNativeArtifactsOnDesktop

Disabled Preview Features:
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_metricsArtifactsOnDesktop
PBI_b2bExternalDatasetSharing
PBI_onObject
PBI_publishDialogsSupportSubfolders
PBI_enhancedReportFormat
PBI_qnaImproveLsdlCopilot
PBI_UserInstalledNetezzaODBCDriver
MashupFlight_EnableOracleBundledOdacProvider
PBI_dataFunctionActions

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

PowerBIUserFeedbackServices_IsReported:
True

DPI Scale:
100%

Supported Services:
Power BI

Formulas:

section Section1;

shared Query1 = let
Source = ""
in
Source;

shared Query2 = let
Source = Sql.Database(
"dalsql080.NA.XOM.COM",
"PIMS_API_DB"
),
Result = Value.NativeQuery(
Source,
"SELECT * from PrCase
WHERE SolutionID ='498284604'"
)
in
Result;

WebView2 Runtime Version:
146.0.3856.62

WebView2 SDK Version:
1.0.2365.46

The query which I used is the below one, where I haven't used the parameters yet 
let
Source = Sql.Database(
"",
""
),
Result = Value.NativeQuery(
Source,
"SELECT * from PrCase
WHERE SolutionID ='498284604'"
)
in
Result

Thank you!

[Natarajan_M · Accepted solution]

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]

Hi @Sanjota05  , Could you please share your exact Power Query, ensuring you mask your server details? I need to take a look at it.
Thanks

[Sanjota05]

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

[Natarajan_M]

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

Thanks 

[Sanjota05]

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

[v-hashadapu]

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

[Sanjota05]

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?

[Natarajan_M]

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]

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!

[Natarajan_M]

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

[Sanjota05]

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

[Natarajan_M]

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?

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