Forum Discussion
Dynamic M Query Parameters not working when I make slicer selection
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
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
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.
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 DynamicQuerykushanNa No Dice,
It eneded up giving me the original 'Conversion' error. (I've pasted it at the bottom)
I copy and pasted the code. Although it works in the Power Query Editor, the visuals break once I Close & Apply the update to the report.
Once again, thanks for all your help so far.
I really think v-prasare is on to something.
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
