Forum Discussion

Swapnil_'s avatar
Swapnil_
Regular Visitor
3 years ago
Solved

Change Text column to Date Format

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?

Data Protection
General Comment
Need Help
Show and Tell
Tips and Tricks
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    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!

2 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    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!