Leading Zero

[mlilkas]

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

[mucuracat]

Hi, I found the solution here. Had the same issue.

https://radacad.com/add-leading-zeros-to-a-number-in-power-bi-using-power-query

[darrenfishell]

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]

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]

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

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.

[SJ]

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]

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]

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

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

[seanrichmkv]

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

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

[asocorro · Accepted solution]

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.

[mucuracat]

I found the solution here:

https://radacad.com/add-leading-zeros-to-a-number-in-power-bi-using-power-query

[cuiping]

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

[Maryann]

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

[SJ]

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

[Maryann]

Thanks SJ , This looks like a good fix .

[mlilkas]

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]

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

[asocorro]

I meant here:

[vlookup]

Just what the DR ordered.  Thanks!