Forum Discussion

Terrassa's avatar
Terrassa
Icon for Helper I rankHelper I
2 years ago
Solved

Remove leading zero's for numerical values in alphanumerical column

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?

Need Help

3 Replies

  • johnbasha33's avatar
    johnbasha33
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Terrassa
      Icon for Helper I rankHelper I
      2 years ago

      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)

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.