Forum Discussion

tmendoza's avatar
tmendoza
Resolver I
9 months ago
Solved

Dynamic M Query Parameters not working when I make slicer selection

You folks have always been a great help to me.  What am I doing wrong with my Dynamic M Query Parameter set up? I'm trying to speed up my Direct Query by utilizing Dynamic M Query Parameters. I fo...
Need Help
  • parry2k's avatar
    parry2k
    8 months ago

    tmendoza For everyone, it is fixed, and the solution was to remove "&" from the table name "P&L_GLDQ" 🤷

v-prasare's avatar
v-prasare
Community Support
9 months ago

Hi tmendoza,

Thanks for confirming that your Period column is a Whole Number. The issue might occurring because the current M query wraps the numeric parameter in single quotes. When Power BI applies the slicer selection, those quotes cause the value to be treated as text, which breaks query folding and triggers the error:
“Conversion of an M query in table 'P&L_GLDQ' into a native source query aborted due to too many failures.”

 

To resolve this, remove the quotes and ensure the parameter type is correctly set up.

 

Try this below M query:

 

let
    source = Sql.Database(
        "MCMC.MadeUpServer.com",
        "DIY_MadeUpDataBase",
        [
            Query = "
                SELECT * 
                FROM dbo.vPLGL_ISDashboard
                WHERE Period = " & Number.ToText(fiscal_period)
        ]
    )
in
    source

 

 

Set the parameter fiscal_period as Whole Number (or Decimal Number) in Manage Parameters. Bind it to the Period[Period] field from your imported Period table.

 

Use only the single query step shown above. Do not include any transformations (renamed columns, type changes, filters, etc.).
If “View Native Query” is greyed out in Power Query, folding is already broken and the parameter will not work.

 

Relationships between the imported Period table and the DirectQuery table are not required for the dynamic parameter to work.
You can disable them temporarily if needed for troubleshooting.

 

Removing the quotes ensures the numeric parameter folds correctly into the SQL query.
With the parameter bound properly and no transformations applied, the slicer will dynamically filter the DirectQuery data without breaking native query folding.

 

 

 

Thanks,

Prashanth

tmendoza's avatar
tmendoza
Resolver I
9 months ago

Prashanth,

 

Thanks for your input. Your feedback makes a lot of sense to me. The "View Native Query" advice was a helpful hint.

Unfortunately my dashboard is still disagreable.

 

I copied and pasted your M query into the Advanced Editor and the query did run successfully. 

However, I do see that the "View Native Query" option is greyed out.

Due to this, the parameter is still not working. I get the same error: â€śConversion of an M query in table 'P&L_GLDQ' into a native source query aborted due to too many failures.”

From what I've read, this should be working at this point. I really don't understand what else we're missing.

I will continue to troubleshoot with this new information and let you know if I have any success.

Thanks again!

Below are the the latest screen captures of my query and parameter.

 

let

    source = Sql.Database
            (
                "MCMC.MadeUpServer.com",
                "DIY_MadeUpDataBase",
        [
            Query = "
                SELECT * 
                FROM dbo.vPLGL_ISDashboard
                WHERE Period = " & Number.ToText(fiscal_period)
        ]
    )
in
    source

 

 

  • v-prasare's avatar
    v-prasare
    Community Support
    9 months ago

    Hi tmendoza ,

    The key detail is that using Sql.Database() with a Query= argument automatically disables query folding. When folding stops, Power BI cannot push the parameter back to SQL, which is why the “View Native Query” option remains disabled even though the script appears to contain only a single step.

    To restore folding and enable the dynamic parameter behavior, please replace the current script with the Value.NativeQuery() pattern below:

    let
    Source = Sql.Database("MCMC.MadeUpServer.com", "DIY_MadeUpDataBase"),
    DynamicQuery = Value.NativeQuery(
        Source,
        "
            SELECT *
            FROM dbo.vPLGL_ISDashboard
            WHERE Period = @Period
        ",
        [Period = fiscal_period]
    )
in
    DynamicQuery
     

    After applying this change, View Native Query should become available, and the slicer-driven parameter will correctly fold back into SQL without triggering the conversion errors.

     

     

     

    Thanks,

    Prashanth

    • tmendoza's avatar
      tmendoza
      Resolver I
      9 months ago

      Thank you very much for your input.

      I think we are very close to having this solved.

      I've followed your steps above and it works out in the Power Query editor, (I can see the data load for the correct period).

      However, when 'Close & Applying' it to the dashboard report, I run into this:

      Once we have the above solved, I think I'll be all set.

      • kushanNa's avatar
        kushanNa
        Super User
        9 months ago

        Nice explanation v-prasare , Enjoyed reading thru your comments 

         

        tmendoza I wonder if this error go back to v-prasare explanation about quotes maybe try removing them as he suggested ? 

         

        try this code ? 

        let
    Source = Sql.Database("MCMC.MadeUpServer.com", "DIY_MadeUpDataBase"),
    DynamicQuery =
        Value.NativeQuery(
            Source,
            "SELECT * FROM dbo.vPLGL_ISDashboard WHERE Period =" & Number.ToText(fiscal_period)
            
        )
in
    DynamicQuery