Format date from text

[Anonymous]

I have a column that has years in YYYY text format. I want to convert it to date format (YYYY)

Here are the steps that I carried out:

1. I change the data type of the column to text. Let's call this OldYearColumn

2. I create a custom column and name it as NewYearColumn

3. I use the formula Date.FromText[OldYearColumn]

It gives me this error:

Expression.Error: We cannot apply field access to the type Function.
Details:
Value=Function
Key=OldYearColumn

I also tried this formula: Date.FromText(Text.From[OldYearColumn]) but this also gives me the same error. Please help.

[Anonymous]

@Anonymous  - I don't think you can convert a year to date format. Why does it need to be in date format?

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Anonymous]

@Anonymous  it needs to be in YYYY format because the cell values are results from a test that is done once every year.

[Anonymous]

It is already in YYYY format, so I don't understand what else you want to do?

[Anonymous]

@Anonymous  I should have been more clear. Currently, the date is in text format. I would like to convert it into YYYY date format and build bar graphs with x-axis as years 2013, 2014, 2015,....

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

If your year column is of text type like 2017, 2018, 2019,

just convert the data type to number.

You could use it in the x-axis of a chart.

If you want to create a column like 2017/1/1, 2018/1/1, 2019/1/1

Just add a custom column

Text.Combine({Text.From([year], "en-US"), "1/1"}, "/")

Then convert the data type to date.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous - Date data type requires a year, month and day. Do you want to assign a particular day and month, or are you only interested in the year? When you have a date data type, you can format to only show the year, but a year alone is not a date data type. 

All that said, I think the chart should look just fine with the field just as it is. Could you share a screenshot of your chart and describe how you would like it to be different?

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.