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 there are cases without any leading zero.

How can I add this missing information with PQ?

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"})

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    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"})
  • serpiva64's avatar
    serpiva64
    Solution Sage
    3 years ago

    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!

     

     

     

  • 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