Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

9 REPLIES 9
DanielClyburn
New Member

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.

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
Advocate IV
Advocate IV

In M language on Power Query Editor you can use:

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

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
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler

 

Thank you sir.

Anonymous
Not applicable

@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?

That formula will actually already do that.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.