Forum Discussion
Months are not reflected as dates
Hi, I hope you can help me with the following. I have a column with the names of the months. When I change the data type from text to date, I get errors. I have already tried to change it by clicking on 'Change type' --> Using locale but that does not seem to be the problem. Any other suggestions on how to fix this? Thanks!
You could create a "Custom Column" using this code below:
Date.FromText(
Number.ToText(
if [Month] = "January" then 1
else if [Month] = "February" then 2
else if [Month] = "March" then 3
else if [Month] = "April" then 4
else if [Month] = "May" then 5
else if [Month] = "June" then 6
else if [Month] = "July" then 7
else if [Month] = "August" then 8
else if [Month] = "September" then 9
else if [Month] = "October" then 10
else if [Month] = "November" then 11
else if [Month] = "December" then 12
else null
)
& "/1/"
& Number.ToText(Date.Year([Year]))
)Then you should be able to change the data type of that new column to Date.
-Steve
4 Replies
- amitchandak
Super User
Anonymous , You can not convert month name to date.
In case you need a sorting correction, create a sort column with a month number and mark that as the sort column
Thanks! My problem is that I import another table with a date column (year and month) which is recognized as a date. This is the date column from my other table. Because this table recogizes the month as a date, I can not combine the data from the two tables in one visual. I hope my explanation makes sense... Any idea how to fix this?
SteveHaileySolution Specialist
You could create a "Custom Column" using this code below:
Date.FromText(
Number.ToText(
if [Month] = "January" then 1
else if [Month] = "February" then 2
else if [Month] = "March" then 3
else if [Month] = "April" then 4
else if [Month] = "May" then 5
else if [Month] = "June" then 6
else if [Month] = "July" then 7
else if [Month] = "August" then 8
else if [Month] = "September" then 9
else if [Month] = "October" then 10
else if [Month] = "November" then 11
else if [Month] = "December" then 12
else null
)
& "/1/"
& Number.ToText(Date.Year([Year]))
)Then you should be able to change the data type of that new column to Date.
-Steve
