We can't automatically convert the column to Date type

[mork]

Hello,

I have an excel file with a table. In the table there is a column with months. I was trying to display a graph with those months in the axis but power BI put the months in the axis alphabetically instead of chronologically. Then I noticed that power BI recognises this column as text. When I tried to change the type to date I got this error "We can't automatically convert the column to Date type".

I tried changing the column to Date type within the query editor and even though the column changes to Date type the values of the column show up as "error".

bellow is said column.

Months

June

July

August

September

October

November

December

January

February

March

April

May

Why does that happen and how can I fix it?

[nhideal]

Hi,

Click on three dots at the top right side of field, click on edit query, in query editor change the format of the column from text to date. close and save, the problem will be resolved.

[konstantinos]

@mork  Months are text and cannot converted to date format. 

You need to add a column with month number in the year ie. 

Month    MonthOfYear

January      1

February     2

etc              etc

Then go to modelling tab - select month column -> Sort by -> check "MonthOfYear"

If you cannot enter the source table use "Enter Data" to create a new table and relate it with the table you have with month columns and use the new table column months for the chart

[Sean]

@mork If you are lucky enough that all your data is in the same year

Use this shortcut - Create a New Calculated Column => Date = 'Table'[Month Name]&" 1, 2016"

And that's it - if you have only 1 year!

[konstantinos]

@mork Year should be enough if you follow @Sean method and instead of static Year the formula will be 

Date = Table[Month Name] & "1, " & Table[Year]

But what exact are you trying to achieve. You can add the sort column & the year also in the excel file and try both ways

Btw @Sean really clever transformation

[Sean · Accepted solution]

@mork Yes of course if you do have a Year column you should be fine.

Date = Table[Month Name]&" 1, "&Table[Year]

[mork]

@Sean @konstantinos

I just added the year next to the month inside the excel file and it got recognised as date type! Thanks for the help though and for pointing out what was wrong!

[mork]

@Sean This unfortunately won't work cause my data already spans through different years.

Actually this excel file contains data about the next 12 months. Meaning that it will always have the same 12 lines one for each month but the months inside the month column will change.

For example right now the values begin with June 2016 and end with May 2017. Next month the month column will still be 12 lines but it will begin from July 2016 and end with June 2017.

Basically a table that looks like bellow for this month.

Months        EstimatedSalesForNext12MonthsColumn

June                    

July

August

September

October

November

December

January

February

March

April

May

And next month the table in the excel file will be something like bellow:

Months        EstimatedSalesForNext12MonthsColumn                    

July

August

September

October

November

December

January

February

March

April

May

 June

The second column is just an example to get an idea about the nature of the table but I kept it empty.

[mork]

@konstantinos What should I input in the existing column to have Power BI recognise it as date? Would the year next to the month be enough? The column is from an editable excel file and I can change the values of the column as long as they still have months in them. I would prefer that instead of building a new column.