Forum Discussion

Arul's avatar
Arul
Super User
5 years ago
Solved

Date Conversion

Hi All,

 

Could one help me on how to convert the below attached text column into proper date time either in Power query editor or in DAX?

 

Thanks,

-Arul

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Arul ,

     

    Try this calculated column

    Column =
VAR _y =
    RIGHT ( [Date], 4 )
VAR _d =
    RIGHT ( LEFT ( [Date], 10 ), 2 )
VAR _mon =
    SWITCH (
        LEFT ( [Date], 3 ),
        "Jan", 1,
        "Feb", 2,
        "Mar", 3,
        "Apr", 4,
        "May", 5,
        "Jun", 6,
        "Jul", 7,
        "Aug", 8,
        "Sep", 9,
        "Oct", 10,
        "Nov", 11,
        "Dec", 12
    )
VAR _h =
    RIGHT ( LEFT ( [Date], 13 ), 2 )
VAR _min =
    RIGHT ( LEFT ( [Date], 16 ), 2 )
VAR _s =
    RIGHT ( LEFT ( [Date], 19 ), 2 )
RETURN
    DATE ( _y, _mon, _d ) + TIME ( _h, _min, _s )

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Arul , A new column like in Power Query

    DateTime.FromText(Text.combine({ Text.Middle([column],8,2)," ", Text.Middle([column],4,3), Text,Right([column],4),Text.Middle([column],11,8)}))

     

    Adjust start index in middle if needed, like 8 can be 9

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Arul ,

     

    Try this calculated column

    Column =
VAR _y =
    RIGHT ( [Date], 4 )
VAR _d =
    RIGHT ( LEFT ( [Date], 10 ), 2 )
VAR _mon =
    SWITCH (
        LEFT ( [Date], 3 ),
        "Jan", 1,
        "Feb", 2,
        "Mar", 3,
        "Apr", 4,
        "May", 5,
        "Jun", 6,
        "Jul", 7,
        "Aug", 8,
        "Sep", 9,
        "Oct", 10,
        "Nov", 11,
        "Dec", 12
    )
VAR _h =
    RIGHT ( LEFT ( [Date], 13 ), 2 )
VAR _min =
    RIGHT ( LEFT ( [Date], 16 ), 2 )
VAR _s =
    RIGHT ( LEFT ( [Date], 19 ), 2 )
RETURN
    DATE ( _y, _mon, _d ) + TIME ( _h, _min, _s )

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.