Query Editor - Issue with coverting date format when year changes when date format is Day DD MMM e.g Sat 28 Dec

[Kerryx]

[URL]https://imgur.com/a/2bFUSl2
[/URL]
At the switch over to 2020 it just shows as an error using the following

#"Changed Type" =Table.TransformColumnTypes(Data0,{{"Column1", type date},

Is there an way to handle this?

 

[sandy666]

click on Error and it will tell you what kind of error is

 

[Kerryx]

DataFormat.Error:We couldn't parse the input provided as Date value
Details:
Sat 11 Jan

 

[sandy666]

I think your source table contain dates stored as text not as date

select in source Sat 11 Jan and look at formula bar, if you see the same that means this is a text not a date

 

[Kerryx]

Probably is as it is in format at
Sat 28 Dec
Wed 22 Jan
Sat 1 Feb

everything prior to Wed 1 Jan converts to date ok as I assume it looks and takes current system year for date but runs into trouble when it hits Jan 1 as It cannot determine next year is 2020

 

[sandy666]

I suggest use normal format date: dd/mm/yyyy or in US style: mm/dd/yyyy without custom formatting like: Sat 1 Feb

 

[Kerryx]

Wish I could but data being pulled from a website so gotta work with what I got

 

[sandy666]

data being pulled from a website

you need to convert data to be a proper data (date)
if on website dates are as dates it's ok, but if as text you should convert it manually

btw. Sat 11 Jan is not a date, simply this is a text that pretends to be a date