Forum Discussion
Dynamic M Query Parameters not working when I make slicer selection
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.
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.
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-into-a-native/m-p/1563057#M116483 https://www.reddit.com/r/PowerBI/comments/jo0kdv/unable_to_convert_an_m_query_in_table_xxx_into_a/
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.
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
sourceSet 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