Forum Discussion
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
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.
19 Replies
You can use the FORMAT function to create a user-defined format.
FORMAT( [ColumnName] , "000" ) would give you the desired result.
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.
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
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.
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:
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
You can use the FORMAT function to create a user-defined format.
FORMAT( [ColumnName] , "000" ) would give you the desired result.
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-detection-of-data-types/
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
