Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Alex_
Frequent Visitor

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 ""

 

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@Alex_ 

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.

jdbuchanan71_0-1614960490795.png

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.

 

View solution in original post

4 REPLIES 4
jdbuchanan71
Super User
Super User

@Alex_ 

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.

jdbuchanan71_0-1614960490795.png

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
Super User
Super User

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.