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

Gaelim Holland

Subscribe
Notify of
guest

4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
CAM
CAM
3 years ago

text.trim will remove characters from the beginning and end of the sting. use text.trimStart to remove characters from the beginning only.

Miguel Ribbon
Miguel Ribbon
2 years ago

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.

Last edited 2 years ago by Miguel Ribbon
Ashwin
Ashwin
4 months ago
Reply to  Miguel Ribbon

Is there any way that I can remove only leading zeros

Rob
Rob
2 years ago

Thank you very much!