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

BA_Pete's avatar
BA_Pete
Super 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

  • qmest's avatar
    qmest
    Frequent Visitor
    4 years ago

    Perfect. Thank you!