Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a query that returns a column PTTTIM as text. it displays 324. I want to convert this to a 24 hour format time 00:03:24 and so on based on the text displayed.
I have tried =Time.FromText but it just errors out.
Any help would be appreciated.
Solved! Go to Solution.
Hi, @Firewtc343
As is suggested by @primolee , the format of the text can not be parsed correctly by 'Time.FromText'. I'd like to suggest you manually parse the text. I created data to reprodce your scenario. The pbix file is attached in the end.
Table:
You may add two new steps with the following steps.
= Table.TransformColumns(#"Changed Type",{"PTTTIM",each
let
txt = _,
l = Text.Length(_),
res =
if l=1 then "00:00:0"&txt
else if l=2 then "00:00:"&txt
else if l=3 then "00:0"&Text.Start(txt,1)&":"&Text.End(txt,2)
else if l=4 then "00:"&Text.Start(txt,2)&":"&Text.End(txt,2)
else if l=5 then "0"&Text.Start(txt,1)&":"&Text.Middle(txt,1,2)&":"&Text.End(txt,2)
else if l=6 then Text.Start(txt,2)&":"&Text.Middle(txt,2,2)&":"&Text.End(txt,2)
else null
in
res
}
),
= Table.TransformColumnTypes(Custom1,{{"PTTTIM", type time}})
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Firewtc343
As is suggested by @primolee , the format of the text can not be parsed correctly by 'Time.FromText'. I'd like to suggest you manually parse the text. I created data to reprodce your scenario. The pbix file is attached in the end.
Table:
You may add two new steps with the following steps.
= Table.TransformColumns(#"Changed Type",{"PTTTIM",each
let
txt = _,
l = Text.Length(_),
res =
if l=1 then "00:00:0"&txt
else if l=2 then "00:00:"&txt
else if l=3 then "00:0"&Text.Start(txt,1)&":"&Text.End(txt,2)
else if l=4 then "00:"&Text.Start(txt,2)&":"&Text.End(txt,2)
else if l=5 then "0"&Text.Start(txt,1)&":"&Text.Middle(txt,1,2)&":"&Text.End(txt,2)
else if l=6 then Text.Start(txt,2)&":"&Text.Middle(txt,2,2)&":"&Text.End(txt,2)
else null
in
res
}
),
= Table.TransformColumnTypes(Custom1,{{"PTTTIM", type time}})
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much.
Hello @Firewtc343
By default, Time.FromText will split the first 2 digits into hour, second 2 digits to minute and third 2 digits to seconds. In other words, 324 will become 32:04 which would be an error because hour can only be from 0 to 23.
You either have to import data using "0324", or manually add steps to split PTTTIM column and restructure them.
Best regards,
David