Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
3 years ago
Solved

Add Leading Zeros within String

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 the...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    3 years ago

    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"})
Peter_Beck's avatar
Peter_Beck
Resolver II
3 years ago

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