More than 2 IF conditions to identify different data types in a mixed column not working- PowerQuery

[Anonymous]

I am trying to Add a new Column that uses multiple If conditions to check if a particular column contains a specific text, numbers formatted as text or textual values. The below If conditon works only for the first 2 criteria but adding a 3rd checking criteria does not yield the desired NULL results for numeric values that are formatted as text.

ColumnToCheck	NewColumn
R0	Questions
Is this my life?	Is this my life?
0.5445	null
0.23	null
0.23335	null

The formula is:

= Table.AddColumn(PreviousStep, "NewColumn",
each
if
Value.Is([ColumnToCheck],Text.Type) and not Text.Contains([ColumnToCheck],"R0") then
[ColumnToCheck]
else if
Text.Contains([ColumnToCheck],"R0", Comparer.OrdinalIgnoreCase) then
"Questions"
else if
Value.Is(Number.FromText([ColumnToCheck]),type number) then
null
else
null)

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, you may try what is suggested by @PhilipTreacy . I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may add a new step with the following m codes.

= Table.AddColumn(#"Changed Type", "Custom", each try 
if Value.Is(Number.FromText([ColumnToCheck]),type number) then null 
else null
otherwise 
if Value.Is([ColumnToCheck],type text)
then
if Text.Contains([ColumnToCheck],"R0",Comparer.OrdinalIgnoreCase) 
then "Questions"
else [ColumnToCheck]
else null)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

Hi @Anonymous 

This works

ThisStep = Table.AddColumn(PreviousStep, "Custom", 
    
        each try if Value.Is(Number.FromText([ColumnToCheck]), type number) then null else null
             otherwise if [ColumnToCheck] = "R0" then "Questions" else [ColumnToCheck] 
)

Phil

[artemus]

I think what you want is:

try Number.ToText([ColumnToCheck]) otherwise null