Adding a prefix to data to achieve character length of "5" numeric digits

[Anonymous]

I am working with a dataset that has a column whose data is between 1 and 5 digits, representing an ID.  For the purpose of my process I need to ensure that he output through Power BI is always 5 digits in lenght.

Is there a way that I can add zeroes to the number in this column to create a 5 digit numeric field - ALWAYS.  

For instance:

Original: 8

Desired:  00008

Original: 42

Desired:  00042

Thank you!

[DanielClyburn]

The above fix did not work for me.  I am in Transform Data.  I found the below to help.

Create a custom column equal to below:

= text.padstart([column_name],5,"0")

Column_Name is of course you column, the 5 is how manay total digits you want, and the "0" is where you tell it you want to add 0's to the front.

[CidcleyBarbosa]

This function will works only on text string. You should transform the format to type text. You can do this using Text.From as below:

= Text.PadStart(Text.From([column_name]),5,"0")

[CidcleyBarbosa]

In M language on Power Query Editor you can use:

= Table.TransformColumns(#"previous step", {{"ID", each Text.PadStart(Text.From(_),5,"0"), type text}})

[ekgcorp]

Got this to work from your Post... Thanks...

• Turn on Formula Bar within View Section
• Changed Column Type to Text
• Right Clicked on Column Header, Then Transform and selected Trim.
• then Replaced everything to the right of Column Name (Should be in "Red") with the following... from your Command.
  • , each Text.PadStart(Text.From(_),5,"0"), type text}})

[Greg_Deckler]

Column = FORMAT([Column1],"00000")

[Anonymous]

@Greg_Deckler

Thank you sir.

[Anonymous]

@Greg_Deckler

Thanks os much.  Should this column grow over time, is there a condition which I can create that would make this columns a min of 5 digits by adding zeroes, but honor values of greater than 5 digits.

Thoughts?

[Greg_Deckler]

That formula will actually already do that.

[Anonymous · Accepted solution]

You can use Format DAX function :-

Create a calculated column, something like below :-

NEW_ID = FORMAT(Table1[ID],"00000")

Only drawback is that this new column will be of string type.

Integer won't take zeroes before numeral.