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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
irnm8dn
Post Prodigy
Post Prodigy

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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler

 

Thank you sir.

@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!:
Mastering Power BI 2nd Edition

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.