How to Remove Leading Zeros in Power BI

In this Power BI article, we will see how to remove leading zeros in Power BI using power query with different examples.

Remove leading zeros in Power BI

  • Remove leading zeros used to remove the leading zero values in front of digits in Power BI, we use the Power Query editor trim function to remove the leading zeros in Power Bi.
  • For example, In the below screenshot, you can see the Leading Zero column will be considered as an input column (like 01,02,03, etc.) and the Remove leading Zero columns (1,2,3, etc.) will be the expected output column after removing the leading zero values.
Remove leading zeros in Power BI
Remove leading zeros in Power BI

Remove leading zeros in Power BI using Power Query

Let us see how to remove leading zeros using the Power Query editor in Power BI,

In this example, we will use the products ordered table data, you can download it from here and use it in the Power Bi.

Example 1:

  • Open the Power Bi desktop and load the Products Ordered data table by using the get data option.
  • Then Select the transform data option under the home tab as highlighted below, and it will automatically redirect to the Power Query editor.
Remove leading zeros in Power BI
Remove leading zeros in Power BI

In the Power query editor, select the Add Column-> Add a Custom column as shown below:

Example to convert number to text with leading zeros in Power BI
Remove leading zeros in the Power BI example

In the Custom column window, use the below formula to trim the leading zero and click on the OK button.

= Text.TrimStart([Power Query],"0")
Remove leading zeros in Power BI example
Remove leading zeros in the Power BI example
  • In the below screenshot, you can see that the new custom column has displayed the values removed leading zeros.
  • Click on the Close and Apply option, so that the changes will be reflected on the Power Bi desktop.
= Table.AddColumn(#"Added Custom", "Remove leading Zero", each Text.TrimStart([Power Query],"0"))
Example of Remove leading zeros in Power BI
Example of Remove leading zeros in Power BI

In the below screenshot, you can see that the removed leading zero column displays the expected output in the Power Bi data view.

Remove leading zeros in the Power BI
Remove leading zeros in the Power BI

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

See also  How to Convert Date to Text in Power BI

Example 2:

We will see another example to remove leading zeros in Power Bi using the Power Query editor.

In this example, we will use the below-mentioned data table, and we will remove the leading zeros for the real zipcode column.

Remove leading zeros Power BI dax
Remove leading zeros Power BI dax
  • In the same way load the Products Ordered data table by using the get data option.
  • Then Select the transform data option under the home tab, and it will automatically redirect to the Power Query editor.
  • In the Power query editor, select the Add Column-> Add a Custom column. In the Custom column window, use the below-mentioned formula to trim the leading zero and click on the OK button as shown below:
Text.TrimStart([Real Zip Code],"0")
Remove leading zeros from text string Power BI
Remove leading zeros from text string Power BI
  • In the below screenshot, you can see that the new custom column has displayed the values removed leading zeros.
  • Click on the Close and Apply option, so that the changes will be reflected on the Power Bi desktop.
= Table.AddColumn(#"Added Custom", "Remove leading zeros", each Text.TrimStart([Real Zip Code],"0"))
Remove leading zeros in text field Power BI
Remove leading zeros in the text field Power BI

In the below screenshot, you can see that the removed leading zero column displays the expected output in the Power Bi data view.

Remove leading zeros in text field Power BI example
Remove leading zeros in the text field Power BI example

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

This Power BI article explained how to remove leading zeros in Power BI using the Power Query editor in Power BI with different examples.

You may like the following power bi tutorials:

>