Date Conversion

[Arul]

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

[Anonymous · Accepted solution]

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.

[amitchandak]

@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