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.

     

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.