Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Add a Leading Zero within Column?

I have a column of data in which the lack of leading zero in the single digit numbers is causing plotting order issues.

I would like to add a leading zero to each single digit number, what is the easiest way to do this? I am very new to powerBI.

EG. "84-24M6 - Stage 1" to "84-24M6 - Stage 01"

 

Here is my data:

 

Column_name

84-24M6 - Stage 1

84-24M6 - Stage 2

84-24M6 - Stage 3

84-24M6 - Stage 4

84-24M6 - Stage 5

84-24M6 - Stage 11

84-24M6 - Stage 12

84-24M6 - Stage 13

84-24M6 - Stage 14

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Maybe not the tidiest solution, but if you always have the same string format:

    Table.TransformColumns(Source, {{"Column_name", each Text.BeforeDelimiter(_, "Stage ") & "Stage " & Number.ToText(Number.FromText(Text.AfterDelimiter(_, "Stage ")), "0#"), type text}})

     

    Kind regards,

    JB 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Maybe not the tidiest solution, but if you always have the same string format:

    Table.TransformColumns(Source, {{"Column_name", each Text.BeforeDelimiter(_, "Stage ") & "Stage " & Number.ToText(Number.FromText(Text.AfterDelimiter(_, "Stage ")), "0#"), type text}})

     

    Kind regards,

    JB 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      The solution below is similar to your answer jborro. Text.PadStart might offer the clean that you are looking for.

       

      = Table.AddColumn(#"Filtered Rows", "Custom", each Text.BeforeDelimiter([Column_name], "Stage ")
      & "Stage " &
      Text.PadStart(Text.AfterDelimiter([Column_name], "Stage "),2,"0"))

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thank you. However I am getting this error, any ideas?

         

        Expression.Error: The name 'Filtered Rows' wasn't recognized. Make sure it's spelled correctly.