Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all!
I have a column that shows me the time of the day but without leading zeros:
For instance:
How can I change this within power query?
Thanks in advance
Solved! Go to Solution.
Hi @joshua1990 ,
Add a new custom column like this:
let _time = Text.PadStart(Text.From([time]), 6, "0") in
Text.Combine(
{
Text.Start(_time, 2),
Text.Middle(_time, 2, 2),
Text.End(_time, 2)
}, ":"
)
Working example query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlKK1QGSBgbGBmCWsamZAYRlaWRuaghmGRoamRqbKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [time = _t]),
chgToInteger = Table.TransformColumnTypes(Source,{{"time", Int64.Type}}),
addTimeFormatted = Table.AddColumn(chgToInteger, "timeFormatted", each
let
_time = Text.PadStart(Text.From([time]), 6, "0")
in
Text.Combine(
{
Text.Start(_time, 2),
Text.Middle(_time, 2, 2),
Text.End(_time, 2)
}, ":"
)
)
in
addTimeFormatted
Example output:
Pete
Proud to be a Datanaut!
Hi @joshua1990 ,
Add a new custom column like this:
let _time = Text.PadStart(Text.From([time]), 6, "0") in
Text.Combine(
{
Text.Start(_time, 2),
Text.Middle(_time, 2, 2),
Text.End(_time, 2)
}, ":"
)
Working example query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlKK1QGSBgbGBmCWsamZAYRlaWRuaghmGRoamRqbKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [time = _t]),
chgToInteger = Table.TransformColumnTypes(Source,{{"time", Int64.Type}}),
addTimeFormatted = Table.AddColumn(chgToInteger, "timeFormatted", each
let
_time = Text.PadStart(Text.From([time]), 6, "0")
in
Text.Combine(
{
Text.Start(_time, 2),
Text.Middle(_time, 2, 2),
Text.End(_time, 2)
}, ":"
)
)
in
addTimeFormatted
Example output:
Pete
Proud to be a Datanaut!