Forum Discussion

qmest's avatar
qmest
Frequent Visitor
4 years ago
Solved

Retrieving numbers from column with text and numbers

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 o...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

CNENFRNL's avatar
CNENFRNL
Community Champion
4 years ago
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"