Forum Discussion

mithiladas02's avatar
mithiladas02
Icon for Helper I rankHelper I
4 years ago
Solved

How to separate number from text?

Hi,

I have id number with text. I want to show a new column with Id number only. Just like below. how can i write M code or make custom column?

 

Thanks in advance.

 

Need Help
  • KNP's avatar
    KNP
    4 years ago

    Here's the code...

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMjEzBSKF/NS8jJLE/GIQpRQbCwA=", 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}}),
  #"Split Column by Character Transition" = Table.SplitColumn(
    #"Changed Type",
    "Column1",
    Splitter.SplitTextByCharacterTransition({"0" .. "9"}, (c) => not List.Contains({"0" .. "9"}, c)),
    {"Column1.1", "Column1.2"}
  )
in
  #"Split Column by Character Transition"

    Specifically, the #"Split Column by Character Transition" step.

    Hope this is what you're after.

     

4 Replies

  • KNP's avatar
    KNP
    Icon for Super User rankSuper User
    4 years ago

    Right click on the column and select split, digit to non digit...

     

     

  • mithiladas02's avatar
    mithiladas02
    Icon for Helper I rankHelper I
    4 years ago

    Thank you for your quick response. I wanted to know how to write a syntex in M language without spliting the colum.

    • KNP's avatar
      KNP
      Icon for Super User rankSuper User
      4 years ago

      Here's the code...

      let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMjEzBSKF/NS8jJLE/GIQpRQbCwA=", 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}}),
  #"Split Column by Character Transition" = Table.SplitColumn(
    #"Changed Type",
    "Column1",
    Splitter.SplitTextByCharacterTransition({"0" .. "9"}, (c) => not List.Contains({"0" .. "9"}, c)),
    {"Column1.1", "Column1.2"}
  )
in
  #"Split Column by Character Transition"

      Specifically, the #"Split Column by Character Transition" step.

      Hope this is what you're after.