Dynamic M Query Parameters not working when I make slicer selection

[tmendoza]

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 followed the directions in the below PBI article:

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

I'm using the T-SQL for a single parameter. At the bottom of the page is the code I've inputted into the advanced editor.

As you can see from the code, 'fiscal_period' is the parameter I'm using.

I have an imported table called 'Period' which is related to the Direct Query via 'Period' (see bottom of page).

I've bound the 'Period' column in this table to the 'fiscal_period' parameter.

Things seem to work out up until I actually make a 'Period' selection in the slicer. Making a 'Period' selection in the slicer breaks my visuals and results in the below error message:

"Conversion of an M query in table 'P&L_GLDQ' into a native source query aborted due to too may failures"

I can't find a solution for this.

What am I doing wrong with my Dynamic M Query Parameter set up?

Hoping you folks can give me advice on what I need to do to fix my Dynamic M Query Parameter.

P&L_GLDQ:

let

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

[parry2k · Accepted solution]

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

[parry2k]

@tmendoza, send me a private message with the link to connect. I will check the message and join. I would prefer Teams and Zoom. 

[tmendoza]

Sounds good. I'm going to ask for approval and I will get right back at you.

[parry2k]

@tmendoza very hard to tell what is going on here. Maybe easier is to connect for screen sharing and see if something else is going on. 

[tmendoza]

@parry2k , I'm open to that. How do we connect?

[parry2k]

@tmendoza, there is no setting as such. I'm wondering if you run the query in the backend using SSMS or other tool with the same slicer value, does it work? I'm wondering if there is some issue at the source that the query is not returning the data. 

[tmendoza]

Just checked. Backend is working fine. The data is also returned at the power query editor level and report view. The break specificaly happens once a slicer selection is made using the bound parameter. From what I can see, I'm using the right format for it.

let
    Source = Sql.Database("ABCD.Server.com", "Database",
    [Query=
        "
        
            SELECT *
            FROM dbo.vPLGL_ISDashboard
            WHERE Period = " & Number.ToText(fiscal_period)]
    )
in
    Source

[parry2k]

@tmendoza I did a quick demo on my end, and everything is working as expected. Keep everything in context. I'm filtering the customer table on customerID, which is of the number type.

Step1 : Created a parameter called ParamCustomerID:

Step 2: Created a Direct Query Customer table, which will be filtered on the above parameter value:

let
    Source = Sql.Database("MySQLServer", "MySQLDatabase", [Query="SELECT * FROM Customer WHERE CustomerId = " & Number.ToText(ParamCustomerID)])

in
      Source

After saving the above query, it is filtering the customer table on the ParamCustomerID current value, and in this case, it is value 1

Step 3: Created another table, which is simple IDs and will be used for the slicer, and I called it CustomerSlicer with one column CustomerId:

Step 4: Close and apply (no error at this point)

Step 5: Bind to the parameter,  

- go to model tab

- selected CustomerId column in CustomerSlicer table

- under Advanced -> Bind to parameter -> selected ParamCustomerID

Step 6: Added a slicer, and it using CustomerId column from CustomerSlicer table

Step 7: Added a table visual that has two columns from the Customer table, and it is getting filtered as the slicer value is selected.

[tmendoza]

@parry2k , 

Thanks for your response.

We have a similar set up, period and customer ID are both numeric. 

I followed your steps to the tee and I can see the Power Query editor working and loading the data correctly.

The visual also works when there is no slicer selection.

However, once making a slicer selection with the bound parameter, I'm running into this error message:

Any idea what it could mean?

At this point, I feel like this should have worked by now. Is it possible that there is a dashboard setting that I need to toggle to make the M Query parameter work?

[v-prasare]

Hi @tmendoza,

As we haven't heard back, we would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@kushanNa & @parry2k ,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[tmendoza]

Hey there, I thought I replied to everyone.

No, the community member answer did not resolve my issue.

[kushanNa]

Hi @tmendoza 

What are the requirements for having a relationship between the Periods table and the Direct Query table? Try disabling all the relationships and use the Period column as a slicer to see if it works. I wonder if a continuous loop is running due to the relationships. Also, make sure that the Period column is in the correct format.

[tmendoza]

Prior to the Dynamic M query parameter set up, I built the 'Periods' table to have a '1 to many' relationship with the Direct Query table via 'Period'. It's essentialy a dimention table. Due to this, the Period slicer, which used 'Period' from the 'Periods' table, could filter the direct query via 'Period'.

It was a good thought, but removing the relationship does not seem to solve the issue. The below error message still pops up when I make a slicer selection. When I remove the slicer, the error disapears. It seems to be very sensitive to me using the 'Period' column which is bound to the parameter.

[kushanNa]

oh okay , check if creating a new query source works for you ? https://community.fabric.microsoft.com/t5/Service/Unable-to-convert-an-M-query-in-table-table-name-i...  https://www.reddit.com/r/PowerBI/comments/jo0kdv/unable_to_convert_an_m_query_in_table_xxx_into_a/ 

[tmendoza]

Thanks for the links. They gave me more insight on the limitations of what parameters can be bound. According to the links, I should be fine. There are no data transformations in my power query.

I created another query source and set it up the same way. But no luck. I'm getting the same behavior. The Direct Query works and expresses only the period that is in the parameter. However, when I make a slicer selection with the data field (Period) that's bound to the parameter the direct query breaks.

[v-prasare]

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]

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]

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]

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.

[tmendoza]

Update to the above, I updated @v-prasare query to show Period instead of @Period and the data loaded in the Power query editor ok. It also was closed and applied to the report but resulted in the same 'Conversion' error.

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