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 :
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
ThanksHello 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.
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