Retrieving numbers from column with text and numbers

[qmest]

Hi,

I have a column in a table that mixes several types of values. Numbers, text, symbols and null values. How can i build a separate column that has only the numeric values and ignores all the others. I've tried the following but didn't work:

if [ANSWER_VALUE] is number then [ANSWER_VALUE] else 999

Thanks.

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorViVZydHIG02AiptTAwMisLk4pNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Mixed = _t]),
    #"Only Numeric" = Table.AddColumn(Source, "Numeric", each try Number.From([Mixed])??999 otherwise 999)
in
    #"Only Numeric"

[BA_Pete · Accepted solution]

Hi @qmest ,

Try using this in a new custom column:

Table.AddColumn(
  previousStep,
  "newColumnName",
  each Text.Combine(
    List.RemoveNulls(
      List.Transform(
        Text.ToList([ANSWER_VALUE]),
        each if Value.Is(Value.FromText(_), type number) then _ else null
      )
    )
  )
)

Pete

[qmest]

Perfect. Thank you!