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.
