Adding 0 to id number

[WJ876400]

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

[ryan_mayu · Accepted solution]

@WJ876400 

you can also use DAX to create a column

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

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[WJ876400]

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]

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  !!