Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |