Forum Discussion
Power Query - Date Parameter Binding to my DirectQuery Mode error Converting from #datetime to Date
As other people have said, it looks like a data type mismatch. As the docs say here https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#considerations-and-limitations you'll need to cast your parameter to a Date in the M code for your Power Query query using the DateTime.Date function.
Hi Anonymous and everyone!!!
Today, after researching and finding nothing!!, I started from scratch:
- Created the parameters,
- Direct Queried my Table Value Function (using the parameters).
- Using advanced Editor I changed the fixed values for the recent created Parameters.
- So far, so good!
- Then, created the date table using Calendar function and BIND to the Date Parameter
- Without creating the Slicer, I could grab the date field from my direct Query and put in the visual
- But, straight after I create the slicer, I got the error:
- Maybe there is something wrong in my SQL command!!
---> The very first Advanced Editor (before change to the parameter name) stated this:
The system created the above query using the information I provided when getting SQL data in Direct Query Mode:
- Following all the instructions on the https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters I changed the Advanced Editor to have the following:
- Kept following Microsoft link and instructions:
--- I created the Date Table as below and bound to the Parameter
--- Bound to the parameter
- Again, so far, so godd!
- Notice, that I had not created the Slicer yet, and I could grab the date field from my Query and put in the
visual:
- But, when I create the Slicer, I got the error:
- I guess, Anonymous , that can something I need to fix in my sql command. Instead of Param_Starting_Date, maybe something to tell the system that the field is already a date!!!
Thanks for Anonymous and any other that can give me the so needed help!!!!
Opening the error:
As other people have said, it looks like a data type mismatch. As the docs say here https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#considerations-and-limitations you'll need to cast your parameter to a Date in the M code for your Power Query query using the DateTime.Date function.