Forum Discussion
Relationship between dates
Hi, some time ago I made a CALENDARAUTO() table for date reference. I use in every project i work (dCalendario)
But when I need to create a relationship between dCalendario.Date and table1.DueDate my DueDate field loses his Date type and show as Text type.
I cant use the fields Year or Month from dCalendario because their dont reference my DueDate.
Any idea to solve this? Thanks and sorry for my bad english
Hi leandroparischi ,
Like this?
After creating relationship
This is normal. After the relationship is created using the date column, only the date column of one of the tables can use the hierarchy. You can do more tests yourself.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
leandroparischi - That's really odd. Have you tried setting your columns in both tables back to Date or Date/Time.
Greg_Deckler I tried do this in my DueDate field.
In Power Query, the Due Date field is categorized as a Date type. Shows the date icon and etc.
In the Column Tools tab, it shows the type as Date as well.
But it is not working as a date field and I believe that is the reason for my dCalendario table can't use DueDate field as reference.
leandroparischi , can share some sample screenshot
maybe it would be better to create some surrogate date key in both of tables with the same format with Power Query?
like "YYYYMMDD" formatted as text
Text.Combine({Text.From(Date.Year([Date])), Text.From(Date.Month([Date])), Text.From(Date.Day([Date]))})Hi leandroparischi ,
Like this?
After creating relationship
This is normal. After the relationship is created using the date column, only the date column of one of the tables can use the hierarchy. You can do more tests yourself.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
leandroparischi , Change the data type in edit query mode . If not under column property
