Linking Tables - dates

[Anonymous]

Hi, 

I have a master Date table, and I'm trying to link it to a number of other tables, all with Year and Month. The Year and Month columns in my other tables are stored as Text, which I think might be part of the problem, but I can't seem to change this without getting an error... ??

MASTER DATE TABLE

OTHER TABLES

Is anyone able to help?

Thank you 

Sonia

[Ashish_Mathur · Accepted solution]

Hi,

In the Other Table, generate a genuine date from the Month and Year by using this formula

=1*("1/"&Table2[Month]&"/"&Table2[Year])

Format this new column as Date and give it a meaningful heading.  Now create a relatioship from the Date column (that you just created with the formula above) of the Other Table to the Date column of your caalendar table.

Hope this helps.

[Anonymous]

Thank you so much, @Ashish_Mathur - this seems to have done the trick! 

[Ashish_Mathur]

You are welcome.

[Anonymous]

Hi @Ashish_Mathur

I've tried this same formula in a different table, and now I'm getting the below error message.

Any ideas why?

Thanks again for your help

Sonia

[Ashish_Mathur]

Hi,

The year and/or month column has atleast one blank cell.  Try this

=IFERROR(your formula,DATE(1900,1,1))

Does this work?

[rocky09]

Format the date column in master table with out time stamp. It is better to have a date column in other tables then you can play with the data.
Btw, one table should have unique values.

[Anonymous]

Hi Rocky, 

The reason I have the time stamp is because the main table it's linked to is a list of Facebook posts, which are time stamped.

This table is linked to the main Date Table but I need the others linked too. 

Any suggestions?

Also, how do I convert the dates in my other tables from text?

[rocky09]

You can convert it in query editor.

[Anonymous]

I get an error in query editor, says it can't convert text

[rocky09]

can you share the screenshot of the error?