Persistent Relationship Reversion & Power Query Date Conversion Error in Power BI Desktop

We cannot convert the value #datetime(YYYY, MM, DD, 0, 0, 0) to type Date

use Date.From(#datetime(YYYY, MM, DD, 0, 0, 0))

assuming that YYYY, MM and DD are valid variables on or after 1899-12-30

Hi Akshukla0412 ,

Please see my answers below:

1. Relationship reversion: this is merely a visual thing and does not impact the work of the relationships, what is important when you look at the relationships is that the many and one is on the correct table so on your case:

 Many to one (*:1) from kiva_loans_utf8[funded_time] to Dates[Date] 

Same as

one to Many (1:*) from  Dates[Date] to kiva_loans_utf8[funded_time]

What is important is the metadata that is on the model and not the visual look. But I agree with you it should be consistent and present the information always in the same way.

2. May I ask what type of data is the funded_time column? is it a Date/Time column?

For the calendar table you need to have date format and has lbendlin  you need to convert that probably using a Date.From I would do that at the begginning of your Query and would not add a list with values of DateTime

3. I believe that this problem is related with data types in this case of the relationship you created. Be sure that both columns you are using on the relatinship are Date, do not do a relationship between a Date/Time and a Date or in this case if you are using a Calendar table do not use a Date/Time column for your relationship, because on the date side since you only have a single date value it will assume hour 0 meaning your value will be 01/01/2025 00:00:00 and on the other side you will have for example 01/01/2025 10:35:25 this will cause that there will be no match for the 01/01/2025 00:00:00 and you get a spiked value on blanks and then null or very small values if by any chance the fact table has date at 00:00:00.
This also forces error on the type of axis because the blanks are there and the logic of continuous won't be picked up.

Hope this was helpfull and if you have any other question or I was not clear on any point just let me know and I can try and send you a mockup file with the "errors" and "correct results".

Hi Akshukla0412, 

Thank you for reaching out to the Microsoft fabric community forum. Also thanks to MFelix, lbendlin, for those insights on this thread. I reproduced the scenario again, and it worked on my end. I used my sample data and successfully implemented it.

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.