Forum Discussion
Helper IRemove leading zero's for numerical values in alphanumerical column
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.
Super UserCertainly! 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 !!
Helper IHello 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)