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 others. I've tried the following but didn't work:

 

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

 

Thanks.

 

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

3 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    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
    Icon for Community Champion rankCommunity 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"