Forum Discussion

KunalL's avatar
KunalL
Frequent Visitor
6 years ago
Solved

Convert Data Type from Text to Date and into a Different Date Format

Hi,  New to Power BI and trying my hand with datasets.  Imported a CSV where I found that the date columns in MM/DD/YYYY format have been detected as Text. I would like to have it as DD/MM/YYYY. So...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Coming back to this, I realize the function I proposed is not the right one.  Date.ToText assumes your values are already Date type, but yours are text.

     

    Did you say some of your values are in general format vs date?  Does that mean you input table looks like the column on the left (dates stored as integers and date formats).

     

    If so, you can use a formula like this to convert each type differently.  We may now be beyone the more elegant solution you initially requested, but I will share this anyway in case it helps.

     

    = if Text.Contains([Date], "/") then Date.ToText(Date.FromText([Date]), "dd/MM/yyyy") else Date.ToText(Date.AddDays(#date(1899,12,31), Number.FromText([Date])-1), "dd/MM/yyyy")

     

    The above is an added custom column, and you should be able to convert it to Date in the following step.

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

     

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

HelloKunalL 

 

you can use Table.TransformColumns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDSNzIwMlCK1YlWMgSyTRFcc31DGC8WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    Trans = Table.TransformColumns(Source, {"Date",each Date.FromText(_,"en-US"), type date})
in
    Trans

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy