Add Leading Zeros within String

[joshua1990]

Hi experts!

I have a column with year and week information in this format:

Year-Week

2022-01

2023-03

2023-3

2023-5

As you can see for the week number there are cases without any leading zero.

How can I add this missing information with PQ?

[Peter_Beck]

Hi -

Here is one solution. 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MFSK1YGxjWFsY10kpqlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Year-Week" = _t]),

NewVal = Table.TransformColumns(Source, {"Year-Week", each if Text.Length(_) = 6 then Text.Insert( _,5,"0") else _})
in
NewVal

We are calling Table.TransformColumns, and for each entry checking the length. If the length is too short (i.e. 6 characters) we call Text.Insert on the value and insert a "0", otherwise return the original. 

Hope this helps -

Peter

[Vijay_A_Verma · Accepted solution]

Insert this statement 

= Table.ReplaceValue(Source, each [#"Year-Week"], each if Text.Length(Text.AfterDelimiter([#"Year-Week"],"-"))=1 then Text.ReplaceRange([#"Year-Week"],5,1,Text.PadStart(Text.End([#"Year-Week"],1),2,"0")) else [#"Year-Week"], Replacer.ReplaceValue,{"Year-Week"})

[serpiva64]

Hi,

you can split your column by delimiter -

then add a custom column

and merge again your column

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!