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.

  1. Click Edit Queries.
  2. Click the data type icon on the top of the column.
  3.  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.

change the data type to eliminate leading zeros.

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

  1. Open your Edit Queries menu.
  2. Click Add a New Column.
  3. Enter the Text.Trim function i.eText.Trim([Account Number],”0″).

The text.trim function will eliminate leading zeros in Power BI.

 

This result will allow you to remove leading zeros from both text and numbers.

you can eliminate leading zeros from text and numbers in Power Bi with the Text.Trim function

About the Author

Leave a Reply

Be the First to Comment!

Notify of
avatar