How do I format text column with year to date format in Power BI

[Chuky01]

I imported a dataset with a data column that was just the year (2005 - 2018). However this column is showing in Power BI as text. I have tried to use the formula (blue area) to change it to date format, however as you can see in the green area the "Year" is not lining up. How do I convert text column to a date. My datapoints are annual so I really only need a "Year" column.

[v-lionel-msft]

Hi @Chuky01 ,

Has your problem been solved?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lionel-msft]

Hi @Chuky01 ,

I have a sample data like this.

1. Change type of the column in 'Edit Query' and you'll get the values.

2. Change the format of the column in 'Data View'.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

If you need/want to do it on the query side (e.g., you have data transformations needing a date column), you can use this formula in a custom column

= #date(Number.FromText([Calendar Year]),1,1)

If you first convert the Calendar Year to whole number, you won't need the Number.FromText part.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Greg_Deckler]

@Chuky01 - Could you just use:

=Date([Calendar Year],1,1)

[Anand24 · Accepted solution]

Hi @Chuky01 ,

Go to Power Query Editor and follow below steps:

1. Right Click on Date Column -> Change Type -> Select "Date"

2. Right click again on Date Column -> Transform -> Year -> Year

Alternatively, you can only perform step 1 and then pull 'Year' from date hierarchy when creating visualizations:

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!