Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
otiliagaino
Helper I
Helper I

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

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:

 

otiliagaino_0-1715836008538.png

 

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!

1 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.

View solution in original post

10 REPLIES 10
otiliagaino
Helper I
Helper I

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

otiliagaino_0-1716169620109.png

 

- But, straight after I create the slicer, I got the error:

otiliagaino_1-1716169723763.png

 

- Maybe there is something wrong in my SQL command!!

   ---> The very first Advanced Editor (before change to the parameter name) stated this:

otiliagaino_2-1716169985070.png

          The system created the above query using the information I provided when getting SQL data in Direct Query Mode:otiliagaino_3-1716170096269.png

 

- 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:

otiliagaino_6-1716170471042.png

- Kept following Microsoft link and instructions:

  --- I created the Date Table as below and bound to the Parameter

otiliagaino_4-1716170237114.png

 --- Bound to the parameter

otiliagaino_5-1716170278101.png

- 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:

otiliagaino_7-1716170663383.png

- But, when I create the Slicer, I got the error:

 

otiliagaino_8-1716170805276.png

- 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!!!

otiliagaino_6-1716170471042.png

 

Thanks for @Anonymous and any other that can give me the so needed help!!!!

 

 

 

 

 

 

 

 

 

 

 

 

Opening the error:

otiliagaino_0-1716173877809.png

 

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

Thanks a lot, in advance!!!

Cheers

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
Helper I
Helper I

Hi @Anonymous  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_0-1715919491470.png

 

otiliagaino
Helper I
Helper I

Here you have the Parameter4 and the slicer table.

otiliagaino_1-1715917965266.png

 

otiliagaino
Helper I
Helper I

No ideas?

otiliagaino_0-1715917875851.png

 

Anonymous
Not applicable

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?

vcgaomsft_0-1715916334486.png


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

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

Thanks in advance!

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.