How to Remove Leading Zeros Power in BI
Getting rid of leading zeros can be kind of difficult in Power BI if there is no consistency in the number of zeros. However, I am going to demonstrate a few easy ways to eliminate leading zeros in Microsoft Power BI.
Change the Data Types
One of the little known tricks in Power BI to get rid of leading zeros is to simply change the data type from “text” to “decimal” or “whole number”. This will quickly remove any leading zeros before the other digits. There’s trick will work up to 2 zeros that are positioned before your desired digits. However, it gets questionable with more than two zeros. Also this is not going to work for letter and number combinations. Follow the directions below to get rid of leading zeros before digits.
- Click Edit Queries.
- Click the data type icon on the top of the column.
- Change the data type icon from text to decimal.
If you look at the columns below, we want to change the data type to a numerical value which will eliminate the leading zeros from the text format.
Use the Text.Trim function
If you are used to Excel, you know that the TRIM function is a great tool to help your remove characters. This will only work if the number of zeros is consistent. If not, it kind of becomes difficult because you will have to perform TRIM multiple times. However, using the Text.Trim function will allow you to eliminate leading zeros. But for the most part, this works when you add a new column. The function can be seen below. This will also remove leading zero before text or number or a combination of both.
Text.Trim([012y345],”0″)
the output is 12y345
- Open your Edit Queries menu.
- Click Add a New Column.
- Enter the Text.Trim function i.eText.Trim([Account Number],”0″).
This result will allow you to remove leading zeros from both text and numbers.
text.trim will remove characters from the beginning and end of the sting. use text.trimStart to remove characters from the beginning only.
1.- Converting the data type to TEXT will create/generate errors at some point on the report if the data includes information that is actually numbers; the query will send a “cannot convert the following record to Text” message and will stop the report.
2.- Text.Trim([Account Number],”0″) this function will remove ALL the leading and ending zeroes, not only the leading zeroes.
Is there any way that I can remove only leading zeros
Thank you very much!