Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago

Extract leading zeros from a column with data type text

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 num...
  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    5 years ago

    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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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's avatar
        Syndicate_Admin
        Icon for Administrator rankAdministrator
        5 years ago

        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.

        gerardcanals_0-1627625483765.png

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