cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jch
Microsoft
Microsoft

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

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

2 ACCEPTED SOLUTIONS
yingyinr
Community Support
Community Support

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?

yingyinr_0-1634711566266.png

Power BI Date Formatting

Best Regards

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

View solution in original post

d_gosbell
Super User
Super User

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)

View solution in original post

3 REPLIES 3
d_gosbell
Super User
Super User

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)

View solution in original post

yingyinr
Community Support
Community Support

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?

yingyinr_0-1634711566266.png

Power BI Date Formatting

Best Regards

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

View solution in original post

sm_talha
Resolver II
Resolver II

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.  

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.