Date table and Fact table relationship doesn't seem to be working

[nleuck_101]

Hello All,

I have a Date table and a Fact data table. There is a one to many relationship from the Date table to the Fact data table. I've never ran into this problem before.

Entry Date from Fact table Data Type is Date

date_field from Date table Data Type is Date/Time

I've tried changing the data types to match either as Date or Date/Time and neither seemed to work. Any ideas?

[nleuck_101]

[Greg_Deckler]

@nleuck_101 Well, does you date time field have times in it other than 12:00:00 AM ? If it does, they will not match a pure Date field as a Date without a time component is considered 12:00:00 AM.

[nleuck_101]

@Greg_Deckler 

It does not.

[Greg_Deckler]

@nleuck_101 Can you go into Power Query Editor and change the Date/Time column to just a Date column? Then it should appear with the calendar icon next to it.

[nleuck_101]

@Greg_Deckler 

That didn't work either.

[nleuck_101]

@Greg_Deckler 
I think I found the issue. The Fact table is from a SharePoint list and it is a Text Type in SharePoint because some of the data will move to a SQL table for archiving purposes. I read that moving Dates to SQL from SharePoint doesn't work well unless the SharePoint data is actually a Text column. When I initially bring the SharePoint list in the EntryDate is a text field with the time as 4:00:00am. In Power Query, I change the data type to date. I brought in a different table from our SQL database that has Hire Date as date/time and that seemed to work just fine.

[nleuck_101]

@Greg_Deckler 
Is there a solution to solve this issue?

[Greg_Deckler · Accepted solution]

@nleuck_101 The solution *should* be to convert both columns to Date columns in Power Query Editor. This should drop the decimal component (time) and then everything should match up. 

[nleuck_101]

@Greg_Deckler 
Once I removed them from the visual and added them back in it worked. Odd.

[Greg_Deckler]

@nleuck_101 Huh, that's bizarre.

[LeandroDeodato]

show your relanshionship please.