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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
mlilkas
Frequent Visitor

Leading Zero

Hello,

 

I am trying to set up some store numbers in Power BI so we can view information by store specifically. When I enter the store numbers, PowerBI will list items by leading number instead of in numerical order. For example, Store 007 will only show up as 7, putting it behind store 240.

 

Is there a way in PowerBI to format a leading zero in so something like store 007 would be behind 006, instead of one of my last stores? I cant seem to find any DAX functions to add zeros if the number isnt three digits.

 

 these are currently formated as Whole Numbers

1 ACCEPTED SOLUTION
asocorro
Skilled Sharer
Skilled Sharer

If your source data has leading zeros, then you need to solve this when you import the data (assuming you are not connecting directly to the data source), by making sure you format the column as text in the query editor.  Otherwise, you will need to create a calculated column that pads the store IDs with the correct number of leading zeros.

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

View solution in original post

19 REPLIES 19
mucuracat
New Member

darrenfishell
Advocate II
Advocate II

This method works for leading zeroes, treating the whole number as a string. Add 5 zeroes to the start, then peel off the right 5 of that new string.

 

Client Number (5-digit) = RIGHT("00000" & TABLE[clientNumber], 5)
nchambe
Advocate II
Advocate II

Set your data type to text for proper sorting. I've demonstrated it here: https://sharepointlibrarian.com/2018/09/06/leading-zeroes-in-power-bi-restoring-them-from-auto-detec...

bizbi
Advocate I
Advocate I

@mlilkas

 

If the column (Col) you wish to add zeros isformated as whole numbers, try using this:

 

FORMAT(Table[Col],"000")

This would make your entire column in three digit format.

 

Hope this helps!

Can you break this down further for a new user? I tried creating a custom column and I entered FORMAT([COLUMN], "0000").  I know I am wrong, but any pointers to understanding why or the basics would be greatly appreciated.

In the import stage M Query if you add a new column - you can use this formula:

 

If it is a number you are trying to convert do this:

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

The "Text.From([Column])" is needed only to change the format from number to text as numbers cannot be formatted into text.

 

If it is in Text format already do this:

Text.PadStart([Column],5,"0")

 

In the DAX report you can use the following:

FORMAT(['Column'),"000")

 

Hope this works for you.

Soren

Can you break this down further for a new user? I tried creating a custom column and I entered FORMAT([COLUMN], "0000").  I know I am wrong, but any pointers to understanding why or the basics would be greatly appreciated.

seanrichmkv
Frequent Visitor

You can use the FORMAT function to create a user-defined format.

 

FORMAT( [ColumnName] , "000" ) would give you the desired result.

seanrichmkv
Frequent Visitor

You can use the FORMAT function to create a user-defined format.

 

FORMAT( [ColumnName] , "000" ) would give you the desired result.

asocorro
Skilled Sharer
Skilled Sharer

If your source data has leading zeros, then you need to solve this when you import the data (assuming you are not connecting directly to the data source), by making sure you format the column as text in the query editor.  Otherwise, you will need to create a calculated column that pads the store IDs with the correct number of leading zeros.

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

Otherwise, you will need to create a calculated column that pads the store IDs with the correct number of leading zeros.

How exactly can we performe above? thank you

I tried this solution but it gave me an error. I believe my problem is that the column I want to retain the leading zeroes on is the result of Max(column_name) .   My table is grouped by booking_ID, getting  max(column), and trying to use the result to merge booking_ID with max(seq) as a key to another table.

So my problem is without the leading zeroes, a one to one is turning into a many to many

12864     13

128641   3    are both coming out 1286413 creating many to many for the join.

When I do a format( max(seq), '000') I get an error.

 

Also changing the format to text doesn't add the leading zeroes.

thanks

Hi Maryann,

 

Tried a similar piece and have no issues but not sure what you are doing wrong.

The formula looks to be missing [] and the location it is pulling data from but not sure.

 

What you could try to make them unique is an "&" statement.

 

column 1 &" - "&max(Seq)

 

column 1 = column containing 12864 and 128641

 

This should work unless the error is in your: max(seq).

 

Hope this makes sense.

SJ

 

 

Thanks SJ , This looks like a good fix .

 

Thanks Asocorro, I was able to pad the data. To add the leading numbers I used format(column,"000"). I did not have the "" before so it was not doing anything when I entered the zeros.

Hmm, I am trying to pad the numbers but FORMAT doesnt seem to be working, and I cant find a DAX formula that works either. Text.Pad doesnt seem to exist in Power BI

I meant here:

 

 

text.png

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

Just what the DR ordered.  Thanks!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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