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

[Akshukla0412]

I am encountering a critical and persistent issue in Power BI Desktop where a manually set One to many (1:*) relationship between my Dates table and my fact table (kiva_loans_utf8) consistently reverts to an incorrect Many to one (*:1) cardinality after saving, even in a brand new Power BI file. This prevents proper data filtering and visualization. Furthermore, when attempting to build a date table in Power Query, I encounter a recurring Expression.Error: We cannot convert the value #datetime(YYYY, MM, DD, 0, 0, 0) to type Date. despite using various explicit M-code conversions.

Detailed Problem Description:

I am trying to create a simple line chart to visualize Total Loans (a measure SUM('kiva_loans_utf8'[funded_amount])) over time, using a Dates dimension table.

The funded_time column in kiva_loans_utf8 (imported from SQL Server) contains Date/Time values. The Dates table is intended to be a standard date dimension.

Symptoms:

1. Relationship Reversion: When attempting to establish a One to many (1:*) relationship from Dates[Date] to kiva_loans_utf8[funded_time] with Single cross-filter direction, the relationship appears correct upon initial setup. However, immediately upon saving and re-opening the "Manage relationships" dialog (or when reloading the file), it reverts to Many to one (*:1) from kiva_loans_utf8[funded_time] to Dates[Date]. This happens consistently, preventing the Dates table from filtering the kiva_loans_utf8 table.

2. Power Query Date Conversion Error: When attempting to build the Dates table directly in Power Query using M-code (including List.Dates, Date.From, and Table.FromList with explicit type definitions), I repeatedly encounter the error: Expression.Error: We cannot convert the value #datetime(YYYY, MM, DD, 0, 0, 0) to type Date. Details: Value=DD-MM-YYYY 12:00:00 AM Type=[Type]. This prevents the Dates table from being correctly loaded with pure date types.

3. Line Chart Visualization Issues: As a direct result of the relationship reversion, the line chart displays the Total Loans measure as an initial spike representing the grand total, followed by a flat line at zero or very low values, because the date dimension cannot correctly filter the fact table. X-axis formatting options (like Type and Units) are also consistently missing or behave incorrectly (e.g., Type showing Categorical when it should be Continuous for a date column).

Steps to Reproduce (Performed in a NEW, Blank Power BI File after Reinstallation):

1. Open a brand new, blank Power BI Desktop file.

2. Get Data from SQL Server: Import the kiva_loans_utf8 table from SQL Server.

3. Prepare kiva_loans_utf8 in Power Query:

   • Go to Transform data (Power Query Editor).

   • Select kiva_loans_utf8.

   • Duplicate funded_time column, rename it to Funded_Date_PQ, and set its data type to Date.

4. Attempt to Create Dates Table in Power Query (Using the most recent M-code which still failed):

   • Create a New Source -> Blank Query.

   • Open Advanced Editor and paste the following M-code (this is the version that last resulted in the Expression.Error😞

     let
         // Get Min and Max Dates from the kiva_loans_utf8 table
         SourceTable = kiva_loans_utf8, // Ensure this matches your table name exactly
         MinDate = Date.StartOfYear(List.Min(Table.Column(SourceTable, "funded_time"))),
         MaxDate = Date.EndOfYear(List.Max(Table.Column(SourceTable, "funded_time"))),
     
         // Generate a list of dates (these are datetime values with 00:00:00 time)
         DateList_DateTime = List.Dates(MinDate, Duration.Days(MaxDate - MinDate) + 1, #duration(1, 0, 0, 0)),
     
         // Explicitly convert each datetime value in the list to a pure date value
         DateList_PureDate = List.Transform(DateList_DateTime, each Date.From(_)),
     
         // Convert the pure date list to a table (it will create a default column like "Column1")
         DateTable_Initial = Table.FromList(DateList_PureDate, Splitter.None, null, null, ExtraValues.Error),
     
         // Rename the default column "Column1" to "Date"
         #"Renamed Columns" = Table.RenameColumns(DateTable_Initial,{{"Column1", "Date"}}),
     
         // Set 'Date' column to Date type explicitly
         #"Changed Type - Date" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
     
         // Add Year, Month, Quarter, and Day columns
         #"Added Year" = Table.AddColumn(#"Changed Type - Date", "Year", each Date.Year([Date]), type number),
         #"Added Month Number" = Table.AddColumn(#"Added Year", "Month No", each Date.Month([Date]), type number),
         #"Added Month Name" = Table.AddColumn(#"Added Month Number", "Month Name", each Date.MonthName([Date]), type text),
         #"Added Quarter" = Table.AddColumn(#"Added Month Name", "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
         #"Added Day" = Table.AddColumn(#"Added Quarter", "Day", each Date.Day([Date]), type number),
     
         // Sort by Date (This is fine to keep)
         #"Sorted Rows" = Table.Sort(#"Added Day",{{"Date", Order.Ascending}})
     in
         #"Sorted Rows"

   • Click Done. This is where the Expression.Error occurs.

5. (If the PQ error did not occur, and you proceeded) Create Total Loans Measure: Total Loans = SUM('kiva_loans_utf8'[funded_amount]).

6. (If the PQ error did not occur, and you proceeded) Create Relationship in Model View: Delete any auto-detected relationships. Manually create a new relationship:

   • From table: Dates_PQ (or whatever the new date table is named) -> Date

   • To table: kiva_loans_utf8 -> Funded_Date_PQ

   • Cardinality: One to many (1:*)

   • Cross-filter direction: Single

   • Make this relationship active: Checked

   • Save, close, and immediately re-open "Manage relationships" to observe the reversion.

Troubleshooting Steps Already Attempted (All Failed):

• Verified Total Loans Measure: Confirmed SUM('kiva_loans_utf8'[funded_amount]) works correctly in a Card visual (shows 671K).

• Verified Dates Table DAX: Initially used CALENDAR(MIN('kiva_loans_utf8'[posted_time]), MAX('kiva_loans_utf8'[funded_time])) and then corrected to CALENDAR(MIN('kiva_loans_utf8'[funded_time]), MAX('kiva_loans_utf8'[funded_time])).

• Checked Date Data Types: Ensured Dates[Date] and kiva_loans_utf8[funded_time] (and Funded_Date_PQ) are set to Date/Time or Date types respectively.

• Cleared All Filters: Ensured no report/page/visual filters were active.

• Adjusted Line Chart Y-axis Display Units: Tried Auto, Thousands (K).

• Changed Visual Type Back and Forth: Tried converting to Column chart and back to Line chart to force visual refresh.

• Verified Summarization Method for Measure: Confirmed Total Loans is (Do not summarize) in visual field well.

• Manually Set X-axis Type (Continuous/Categorical): The Type option is consistently missing from the X-axis formatting pane when Date column is used.

• Attempted Drill-down/Drill-up: Arrows appear but show no result or incorrect result (e.g., blank).

• Created "YearMonth" Calculated Columns: YearMonth = FORMAT(Dates[Date], "YYYY MMM") and YearMonthKey = FORMAT(Dates[Date], "YYYYMM") with correct sorting, but issue persisted.

• Clean Reinstallation of Power BI Desktop: Uninstalled and reinstalled Power BI Desktop to ensure no software corruption. Problem still occurs in a new file.

• Imported Data from SQL Server (into new file): Used SQL source to avoid potential CSV import issues, but problem persists.

• Created Funded_DateOnly Column in Power Query: Explicitly extracted date part from funded_time in Power Query and set its type to Date, then attempted to relate to this new column. This is where the Expression.Error in PQ occurs during table creation.

Environment Details:

• Power BI Desktop Version: (Go to File > About in Power BI Desktop and provide the full version number, e.g., "Version: 2.129.742.0 64-bit (May 2024)")

• Operating System: (e.g., Windows 10 Pro, Version 22H2, OS Build 19045.4412)

[lbendlin · Accepted solution]

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

[MFelix · Accepted solution]

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

[v-kpoloju-msft · Accepted solution]

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.

[v-kpoloju-msft]

Hi @Akshukla0412,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @Akshukla0412,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-kpoloju-msft]

Hi @Akshukla0412,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[MFelix · Accepted solution]

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

[lbendlin · Accepted solution]

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