Forum Discussion
If cell is number then cell, else empty
Hi! I have some data like the following:
| % |
| 25 |
| 15 |
| Some text |
| Words typed |
| 75 |
I need to work with just the numbers in the column. How can I delete the text data without removing the whole row? A new column like the following would be ideal, but it returns "" in every row (I suspect it is testing if the whole column is numbers).
Just% = IF(ISNUMBER([%]), [%], "")
if Value.Is([%], Int64.Type) then [%] else ""
You are better off doing the transformation in powerquery I think. It is 3 steps to get to what you are looking for.
- Duplicate your column
- Convert the new column to decimal data type (this will make all text values error)
- Replace errors with null.
I have attached my sample book for you to look at. Right click on the table and go to Edit Query to see the steps.
4 Replies
- jdbuchanan71
Super User
You are better off doing the transformation in powerquery I think. It is 3 steps to get to what you are looking for.
- Duplicate your column
- Convert the new column to decimal data type (this will make all text values error)
- Replace errors with null.
I have attached my sample book for you to look at. Right click on the table and go to Edit Query to see the steps.
Thanks - this worked perfectly!
- amitchandak
Alex_ , refer if this code in the blog can help
Hi amitchandak
Thanks for your help! Unfortunately this doesn't work as there are some dates mentioned within the text. I need to show the value if there is only numbers, and nothing if there is a mix.
