cancel
Showing results for 
Search instead for 
Did you mean: 
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

16 REPLIES 16
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
Helper I
Helper 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!

jdourley
Frequent Visitor

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

jdourley
Frequent Visitor

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

View solution in original post

cuiping
Frequent Visitor

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 .

 

mlilkas
Frequent Visitor

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.

mlilkas
Frequent Visitor

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.