Change Text column to Date Format

[Swapnil_]

Hi,

I am getting data from source as a csv file. The column contains data as Apr 20, May 20 .....till March 23.

I want to change its format from text to date (pref Month Year) , I have tried change type options available in power query(direct date selection as well as Using locals )  for direct but it is not converting the format as I want instead I am getting values like April 20-2023..can you please provide any suggestion?

[m_dekorte · Accepted solution]

Hi @Swapnil_ 

Give this a go: 

Date.FromText( _, [Format="MMM yy", Culture="en-US"])

Depending on how and where you use this formula, you might need to replace the underscore with the column name in square brackets that holds this value: Date.FromText( [Date], [Format="MMM yy", Culture="en-US"])

Here's a sample, you can copy this script into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc4xCsJAGAXhq8jWFr6XEwTEQogWlkuKNSw2oiEYwdsnBH6nm2r4ck7tOO18SP0+p678Is/zi3xGtvMj8lbHyOvwiby8v5HHOvwPZZ1py1O9R3ZlitwMwiAMwiAMwiAMwiAMwmAMxmAMxmAMxmAMxmAMxmAMxtBgaDCs2S8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    GetDate = Table.TransformColumns(Source,{{"Date", each Date.FromText(_, [Format="MMM yy", Culture="en-US"]), type date}})
in
    GetDate

With this result:

Power Query itself doesn't offer any date formatting options - to alter the display of that value - this is something you'll have to save for the application that receives it's output, like Excel or Power BI for example.

Ps. If this helps solve your query please mark this post as Solution, thanks!

[Swapnil_]

Thanks @m_dekorte  it worked!