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 , 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

  • Sanjota05's avatar
    Sanjota05
    Frequent Visitor
    5 months ago

    This 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!