convert text to date

[Aha2022]

 I want to convert this text column to a date column and add the day name to the date.

[RicardoTeixeira]

Function for Power query that converts a text with dates (like 11/5/2023 or  4.5.22 ) to dates based on a kwon format (MM.DD.YYYY).

(Creating a function from an existing endpoint is a simple process. Right-click on the endpoint name and choose Create Function.)

Over the new function, in advace editor past the code:

let
Source = (dateColumn as text,DateSchema as text) => let
MyDelimiter = Text.Range(DateSchema,Text.PositionOfAny(DateSchema,{"/","-",".","\","_"}),1),
DateList = Splitter.SplitTextByDelimiter(MyDelimiter, QuoteStyle.None)(dateColumn),
SchemaList = Splitter.SplitTextByDelimiter(MyDelimiter, QuoteStyle.None)(DateSchema),
TableDate = Table.FromColumns({DateList,SchemaList}),
myDay = "0" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "D" )[Column1]{0}),
myDD = Text.Range(myDay,Text.Length(myDay)-2,2),
MyMonth= "0" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "M" )[Column1]{0}),
myMM = Text.Range(MyMonth,Text.Length(MyMonth)-2,2),
myYear= "20" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "Y" )[Column1]{0}),
myYYYY = Text.Range(myYear,Text.Length(myYear)-4,4),
DateByParts = myYYYY & "-" & myMM & "-" & myDD,
DateCleaned = Date.FromText(DateByParts)
in
DateCleaned
in
Source

Then invoke the function in a new column in power query.

[v-jingzhang · Accepted solution]

Hi @Aha2022 

You can first add a custom column to get the date values without "st/nd/rd/th" into a new column.

Text.RemoveRange([Date], Text.PositionOf([Date], " ")-2, 2)

Then change the new column to Date type. With the new format, dates can be converted into Date type successfully. 

If you want to change the format of dates, you need to apply the data to Power BI Desktop and change the column format there, just like below image. In Power Query Editor, it doesn't support to change the date format when the column is of Date type. 

If you don't want to add a custom column to get the new date values, you can transform the original column with the following M code. Then modify the column type to Date and apply it to Power BI Desktop to change the date format. 

= Table.TransformColumns(#"Changed Type", {{"Date", each Text.RemoveRange(_, Text.PositionOf(_, " ")-2, 2)}})

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Jayee]

Hi @Aha2022 

If you can remove unnecessary characters like th, Rd, st and ",' you can conver it to date it is best to do the cleaning and treating date field as date type then you can perform all the date functions and time intelligence in DAX.

If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!