Power Query - Date Parameter Binding to my DirectQuery Mode error Converting from #datetime to Date

[otiliagaino]

My data types in the my slicer field and parameter are the same. So, I can bind them.

My Power query call a Table Valued Function using the date Parameter. 

When the Parameter is not bind yet, the visual is updated accordingly (using the suggested value that is in the Parameter).

But, when I bin the slicer date field to the date Parameter, my visual does not load and I receive the below msg:

I had checked all my config. and they are by the book. I can no figure it out what I am missing!

Can anyone be of any help, plase!

[otiliagaino]

Hi @v-cgao-msft  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, @v-cgao-msft , 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 @v-cgao-msft and any other that can give me the so needed help!!!!

[otiliagaino]

Opening the error:

[otiliagaino]

Hi @dufoq3 , @lbendlin , @AlienSx , @ribisht17 , @dm-p  can any of you have a look and help me out!!!

Thanks a lot, in advance!!!

Cheers

[cpwebb · Accepted solution]

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#considera... you'll need to cast your parameter to a Date in the M code for your Power Query query using the DateTime.Date function.

[otiliagaino]

Hi @v-cgao-msft  one more test I had done, unsucessfull: I changed my Windows region settings to UK as you can see below, but the error is still ON.

[otiliagaino]

Here you have the Parameter4 and the slicer table.

[otiliagaino]

No ideas?

[otiliagaino]

[v-cgao-msft]

Hi @otiliagaino ,

This error suggests a type mismatch between the expected data type and the provided value.

What is the data type of the field that binds the query parameter? Does it match the query parameter type?

Could you share the specific M code snippet from the Advanced Editor where you're attempting to use the date parameter?

Also check these restrictions:
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[otiliagaino]

Hi @v-cgao-msft , if you can go through the details provided and give any help, I really appreciate it!.

Thanks in advance!