How to Remove Leading Zeros in Power BI

I was working on a Power BI report, and I noticed that some columns, like Employee ID, Customer Code, or Product Number, were stored as text and had leading zeros, for example, 000123.

When I use this column in a report and try to sort or filter the data, I am not getting the correct results. In most cases, I don’t need these extra zeros and want the values to appear in a clean format.

Power BI does not provide a direct option to remove leading zeros, but we can handle this easily using Power Query or DAX, depending on the requirement.

In this tutorial, I will show you different ways to remove leading zeros in Power BI.

Remove Leading Zeros in Power BI using Power Query

Now I will show you how to remove leading zeros in Power BI using Power Query and the Trim function.

For example, I want the Leading Zero column to be the input column and have values like 01, 02, 03, etc. After removing the leading zeros, the Remove Leading Zero column shows the final result with values like 1, 2, 3.

Here, I will use the data from the ordered products table.

To do this, follow the steps below:

1. Open Power BI Desktop and load the Products Ordered table using the Get Data option.
2. Then select the Transform Data option from the Home tab. This will automatically open the Power Query Editor.

3. In the Power Query Editor, go to the Add Column tab and select Custom Column, as shown below.

4. In the Custom Column window, enter the formula below to remove the leading zeros, and then click OK.

= Text.TrimStart([Power Query],"0")

5. Now you can see that the new custom column displays the values without leading zeros.

This is how to remove leading zeros using the Power Query editor in Power BI.

Remove Leading Zeros from Text Columns in Power BI (Power Query)

Now, let us look at another example to remove leading zeros in Power BI using the Power Query Editor. In this example, the data table shown below is used, and the leading zeros are removed from the Zipcode column.

Follow the steps below:

1. Open the Power BI Desktop and load the Products Ordered data table using the Get Data option. Then select the Transform Data option under the Home tab.
2. In the Power Query Editor, go to the Add Column tab and select Custom Column.

2. In the Custom column window, use the following formula to trim the leading zero and click on the OK button as shown below:

Text.TrimStart([Real Zip Code],"0")

3. Then you can see that the new custom column displays the values without leading zeros.

This is how to remove leading zeros using the Power Query editor in Power BI.

Remove Leading Zeros in Power BI using Convert Data Type

In this scenario, leading zeros are removed by changing the data type in Power BI. When a column is converted from Text to Whole Number, Power BI automatically removes the leading zeros.

Here, I want to remove the leading zero from the Quantity column.

To do this, follow the steps below:

1. In Power BI Desktop, load your data, then go to the Table view.

2. Select the table and then select the column from which you want to remove the leading zeros (in this case, Quantity). Under the Column tools tab, expand the Data type dropdown and select Whole number.

3. Then a pop-up will show where you click Yes.

Next, you can see the leading zero removed in the quantity column by changing the data type in Power BI.

Remove Leading Zeros Using Power BI DAX

In this scenario, leading zeros are removed by using a DAX formula. A new calculated column is created, and the DAX expression converts the text value into a number, which automatically removes the leading zeros.

For this example, I will use the same case where we remove the leading zero in the Order ID column.

Follow the steps below:

1. In Power BI Desktop, go to table view, under the Home tab, click New column.

2. Enter the following DAX formula:

Remove Leading Zero = VALUE('ProductsOrdered'[Order ID])

3. The new column will display values like 1, 2, 3 instead of 001, 002, 003, and the leading zeros will be removed.

Note:

This method works only when the column contains numeric values. If the column has text or special characters, this formula may return an error.

In this tutorial, I covered how to remove leading zeros in Power BI. I explained how to remove leading zeros using Power Query with the Trim function, changing the data type from Text to Whole Number, and a DAX formula.

You may like the following Power BI tutorials:

• How to Merge Tables in Power BI
• Convert Number to Text in Power BI
• Sort Slicer by Another Column in Power BI
• Add Index Column Using Power Query Editor in Power BI
• The Best Way to Add a Hyperlink to a Text Column in Power BI