Reply
Member
Posts: 376
Registered: ‎01-31-2017
Accepted Solution

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

[ Edited ]

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!


Accepted Solutions
Highlighted
Regular Visitor
Posts: 20
Registered: ‎05-02-2018

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

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


All Replies
Highlighted
Regular Visitor
Posts: 20
Registered: ‎05-02-2018

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

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.

Super User
Posts: 10,508
Registered: ‎07-11-2015

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

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

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

Proud to be a Datanaut!


Member
Posts: 376
Registered: ‎01-31-2017

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

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

Super User
Posts: 10,508
Registered: ‎07-11-2015

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

That formula will actually already do that.


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

Proud to be a Datanaut!


Member
Posts: 376
Registered: ‎01-31-2017

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

@Greg_Deckler

 

Thank you sir.