Place Numeric Records in a Table

[Bootkie2]

Hello, I have a datasaet that has an answer column that will either contain a number or a text value. I am trying to create 2 lookup tables from master table so that rows with JUST numeric answers fall into 1 table and rows with non-numeric or alpha numeric values fall into another table like so. Is this possible?

[amitchandak]

To check numeric value refer

https://community.powerbi.com/t5/Desktop/Check-whether-characters-are-numeric/td-p/327628

Post that you can use summarize or other functions to separate the data.

[Greg_Deckler · Accepted solution]

You should be able to do something like this:

Table 2 = 
VAR __table = ADDCOLUMNS(ALL('Table'),"__filter",IF(ISERROR([Answer]*1),BLANK(),[Answer]*1))
VAR __table1 = FILTER(__table,NOT(ISBLANK([__filter])))
RETURN
SELECTCOLUMNS(__table1,"QID",[QID],"Answer",[Answer],"RespondentID",[RespondentID])

Table 3 = 
VAR __table = ADDCOLUMNS(ALL('Table'),"__filter",IF(ISERROR([Answer]*1),[Answer],BLANK()))
VAR __table1 = FILTER(__table,NOT(ISBLANK([__filter])))
RETURN
SELECTCOLUMNS(__table1,"QID",[QID],"Answer",[Answer],"RespondentID",[RespondentID])

[jdbuchanan71]

Hello @Bootkie2 

You can add a custome column in the query editor to check the answer type.

try if Int32.From ( [Answer] ) >= 0 and Int32.From ( [Answer] ) <= 9 then "number" else "text" otherwise "text"

Then from there you make two new queries, each that reference the original.  On that pulls just number answers and the other to pull just text and load those two queries into your model.

I have attached my sample file for you to look at.

[Anonymous]

hi @Bootkie2 

Yes!
Here is your answer --> https://community.powerbi.com/t5/Desktop/Test-for-a-number/td-p/36120

Cheers!
A