Remove leading zero's for numerical values in alphanumerical column

[Terrassa]

Hello,

I have a column downloaded from the ERP for the material codes. Some material codes are alphanumerical and thay may or may not start by zero, that's correct. 

But some material codes are fully numerical and they have many zero's in the beginning, I need to remove those zero's.

This is a sample of the data:

I need to remove the leading zero's only for the first lines, the ones that are fully numerical, but not for the other materials with alphanumerical codes like dash or letters.

The amount of leading zero's is not always the same.

Could you please help me to achieve this?

[Anonymous · Accepted solution]

Hi @Terrassa 

Please try the following DAX:

Material Code Cleaned = 
VAR IsNumeric = NOT(ISERROR(VALUE([Column1])))
RETURN IF(IsNumeric, FORMAT(VALUE([Column1]), "0"), [Column1])

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnbasha33]

@Terrassa 

Certainly! You can achieve this in Power Query by creating a custom column that removes leading zeros only for fully numerical material codes. Here's how you can do it:

1. In Power Query Editor, select your Material Code column.
2. Go to the "Add Column" tab and click on "Custom Column".
3. In the custom column formula box, enter the following formula:

```m
= if Text.Middle([Material Code], 0, 1) = "0" then Text.TrimStart([Material Code], {"0"}) else [Material Code]
```

This formula checks if the first character of the Material Code is "0". If it is, it removes all leading zeros using `Text.TrimStart`. If not, it keeps the original Material Code.

4. Click OK to create the custom column.
5. Rename the new column appropriately, such as "Cleaned Material Code".

After adding this custom column, you'll have a new column where leading zeros are removed only for fully numerical material codes, while retaining alphanumeric material codes intact.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Terrassa]

Hello @johnbasha33,

If the first character of the material code is zero, this formula removes all the leading zero's, but I must remove the leading zeros only if all the characters of the material code are numerical. If a material code is alphanumerical but the first character is zero I must not remove that zero.

Examples:

000000002816502000 -> 2816502000 (remove leading zero's)

010-02843-000 ->  010-02843-000 (no change)

01CA0610 -> 01CA0610 (no change)