Extract leading zeros from a column with data type text

[Syndicate_Admin]

Hello

I have a column with both numeric and text values, but in power BI it is defined in text (because otherwise the texts would not be displayed and it is necessary). When this happens, the numeric values in the column are transformed and zeros are added to the left, I would like the original number to be kept without the zeros added. For example:

I have:

And I would like to get:

material_number
28545-30
10020215-138
10020320-138
4000453300
940152
00400-C10
00120-C24
20000095-090

As you can see, the values in yellow/orange are the ones I need to modify, but at the same time, in the same column, I have alphanumeric values that start with zeros that I would like to keep (green).

I can't figure out how to do it. Obviously, if you change the data type to integer, the zeros disappear but it generates error in the fields that are alphanumeric.

Could someone help me?

[v-rzhou-msft]

Hi @Syndicate_Admin 

In addintion to  Greg_Deckler 's reply, try If and Text.Contains to update the code.

New Code:

Desire_material_number
=
if 
Text.Contains([material_number], "-") 
then 
[material_number] 
else 
Text.TrimStart([material_number], "0")

Result:

Finally remove the original column.

Best Regards,

Rico Zhou

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

[Greg_Deckler]

@Syndicate_Admin Perhaps 

=Text.TrimStart([Column1], "0")

[Syndicate_Admin]

Gràcias for the answer, but I do not solve the probema since that function also removes the zeros from the values :

00400-C10

00120-C24

[Greg_Deckler]

@Syndicate_Admin Sorry, I'm not seeing that behavior or I am not understanding your exact needs:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMTDQdTY0UIrVAfEMjYA8IxMoDwJMwISpsbEBUFUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.TrimStart([Column1], "0"))
in
    #"Added Custom"

[Syndicate_Admin]

Look yes! precisely in that image you see the problem to your solution.

As I comment, what I want to get is:

4000453300

00400-C10

00120-C24

With your solution, I get rid of the zeros of all cases. In the original explanation I comment that I just want to remove the zeros from the values that are integers and that the values that are alphanumeric keep the zeros.

That those that have been marked in yellow have the zeros removed and that those that have been marked in green remain the same.

[Greg_Deckler]

@Syndicate_Admin So then just add an if statement that if the value contains a hypen, don't do the trim.

[Syndicate_Admin]

good!

Muhas thanks for the answer! The truth is that there are more casuistry, it should be an if statement that if the value contains an alphanumeric, do not make the cut. The truth is that I do not know if the possibility is successful, nor how it would be done, but for now I have solved the problem by doing several steps with the data transformer.