Forum Discussion

WJ876400's avatar
WJ876400
Icon for Helper IV rankHelper IV
5 years ago
Solved

Adding 0 to id number

Hi

 

I have numerous spreadsheets and other source data that have an ID number, some sources have the ID with a 0 at the beginning and some dont and on my final input I have removed the 0. now I want to add it back in. The ID's have either 8 or 7 numbers in them, I want to add a zero to the beginning of the ID's with 7 numbers. Is that possible?

 

thanks

Need Help

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    WJ876400 

    you can also use DAX to create a column

    Column = FORMAT('Table (2)'[Column1],"00000000")

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey WJ876400 ,

     

    you can do that easily in Power Query with the Text.PadStart function.

    See my example:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorVAdFgysQYShmCaWOIKJATCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Added Custom Column" = Table.AddColumn(#"Changed Type", "Custom", each Text.PadStart(Text.From([ID]), 3, "0"), type text)
in
    #"Added Custom Column"

     

    In your case you just have to edit the PadStart to 7:

    Text.PadStart(Text.From([ID]), 7, "0")

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

     

    • WJ876400's avatar
      WJ876400
      Icon for Helper IV rankHelper IV
      5 years ago

      Hi Denis

       

      Thank you so much for the quick response, sorry I am quite new in using Power BI, do I use this in the query editor? also do I input my ID column where you have "ID" also does it need to be a specifice type on output eg text, number?

      • VahidDM's avatar
        VahidDM
        Icon for Super User rankSuper User
        5 years ago

        Hi WJ876400 

         

        Please try to add a new custom column in Power Query as below:

         Use this code in the custom column:

         

        Text.PadStart(Text.From([ID]), 8, "0")

         

         

        The output will be as follow:

         

         

        Did I answer your question? Mark my post as a solution!

        Appreciate your Kudos  !!