CODEWITHSUNDEEP
powerquery
Randy Minder
Randy Minder

Reputation: 48502

Converting non-standard date strings

I am attempting to import a CSV file that contains a text column (named 'Formatted Date') with dates that look as follows:

Apr 18 '20

I've tried a number of things to convert this to a date field. For example, I created a step that removed the apostrophe and then another step that creates a custom column with this expression:

Date.FromText([Formatted Date],"mmm dd yy")

However this generates an error about an unsupported culture. Is there a way I can do what I want?

Upvotes: 0

Views: 66

Answers (1)

Peter
Peter

Reputation: 12375

Your question is a little ambiguous:

Apr 18 '20

What language does your 3 letter month name come from? English, German? What's the next month then: "May" or "Mai"? And how about the apostrophe: Does it stand for 2000, or do you also have data from the 1900's?

If I replace the apostrophe with "20" and then simply convert the text to date format, it works out perfectly fine:

#"Replaced Value" = Table.ReplaceValue(
    Source,"'","20",Replacer.ReplaceText,{"date"}),
#"Changed Type1" = Table.TransformColumnTypes(
    #"Replaced Value",{{"date", type date}})

So your error is most likely caused by your regional settings. However, when transforming the column type you can click on the "ABC" symbol in your column header (Query Editor) and select "Using Locale ...". In the following pop-up window "Change Type with Locale" You select "Data Type" = "Date" plus the proper "Locale". If you don't know it, you can also try different values until it fits.

Upvotes: 1

Related Questions