How to separate number from text?

[mithiladas02]

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.

[Ashish_Mathur]

Hi,

Read this article - Text.BeforeDelimiter - PowerQuery M | Microsoft Docs

[mithiladas02]

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

[KNP · Accepted solution]

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.

[KNP]

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