We can't automatically convert the column to Date type - text in format of YYYYMMDD to a Date value

[jch]

When we try to convert date values in text fields formatted like 20180620 (YYYYMMDD) to a Date type in Query Editor, we get the error:

"We can't automatically convert the column to Date type"

Is there a more elegant solution than concatenating substrings of this field to look like a date?

Thanks in advance

[v-yiruan-msft · Accepted solution]

Hi @jch ,

Has your problem been solved? If not, you can check the solutions in the following threads with similar problem to yours to see if they can help you solve the problem.

> Change the locale to a country where use YYYYMMDD in Regional setting tab (File > Options and settings > Options > Regional Settings)

Change column type to Date in Power BI

Changing the date format in Power BI - How does it work?

Power BI Date Formatting

Best Regards

[d_gosbell · Accepted solution]

Changing the regional settings of the report is not really an elegant solution and will not work if you have multiple sources with different date formats.

There are a number of better solutions to this issue.

1. If the column is being detected as a number, first change it to text then add another step to convert to a date. This worked fine on my machine when using an unambiguous format like yyyymmdd.

2. The other option is to add a Custom Column where you can use the Date.FromText() function. You can then use the optional second parameter to change the culture used to parse the text. 

eg. Date.FromText( [Column1] , "en-US") would interpret 6/9/2018 as 9 June 2018 (m/d/yyyy)
      Date.FromText( [Column1], "en-AU" ) would interpret 6/9/2018 as 6 Sept 2018 (d/m/yyyy)

[v-yiruan-msft · Accepted solution]

Hi @jch ,

Has your problem been solved? If not, you can check the solutions in the following threads with similar problem to yours to see if they can help you solve the problem.

> Change the locale to a country where use YYYYMMDD in Regional setting tab (File > Options and settings > Options > Regional Settings)

Change column type to Date in Power BI

Changing the date format in Power BI - How does it work?

Power BI Date Formatting

Best Regards

[sm_talha]

is the date format is same through out the whole column? please confirm if it is same in the whole column and do not have any alphapet or character in it. Then try changingt the data type to "Whole Numbers" first and then to Date type.