Test cell value for string

[DanFromMontreal]

Good afternoon all,

I want to create a new column with information based on another column.

This column (NEQ) is formatted as TEXT and contain some string and some numbers (formatted as TEXT).

The new column should evaluate the column NEQ and return in the new column its status based on these criteria.

Spent more than 60 minutes to make it work but could not figure out what is wrong.

Any idea would be appreciated. 

#"Add column Info String" = Table.AddColumn(#"PreviousStep", "Info String", each
if Number.IsNaN(Number.FromText([NEQ])) and [NEQ]="NAS" then "Some String" else
if Number.IsNaN(Number.FromText([NEQ])) and [NEQ] = "" then "Empty String" else
if Number.IsNaN(Number.FromText([NEQ])) then "Other String" else
if Number.FromText([NEQ])<=10 then "<=10" else
if Number.FromText([NEQ])<=100 then "<=100" else ">100"),

Many thanks

[Vijay_A_Verma · Accepted solution]

Number.IsNaN is used to test the presence of 0/0. I believe this is not needed. Use below in a custom column

= try (if Number.FromText([NEQ])<=10 then "<=10" else
if Number.FromText([NEQ])<=100 then "<=100" else
if Value.Is(Number.FromText([NEQ]), type number) then ">100" else "") otherwise
if [NEQ]="NAS" then "Some String" else
if [NEQ] = "" then "Empty String" else
"Other String"

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlWK1YlWMjSCUAaGYNrPMRhMg4nElLQUpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NEQ = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try (if Number.FromText([NEQ])<=10 then "<=10" else
if Number.FromText([NEQ])<=100 then "<=100" else
if Value.Is(Number.FromText([NEQ]), type number) then ">100" else "") otherwise
if [NEQ]="NAS" then "Some String" else
if [NEQ] = "" then "Empty String" else
"Other String")
in
    #"Added Custom"

[Vijay_A_Verma · Accepted solution]

Number.IsNaN is used to test the presence of 0/0. I believe this is not needed. Use below in a custom column

= try (if Number.FromText([NEQ])<=10 then "<=10" else
if Number.FromText([NEQ])<=100 then "<=100" else
if Value.Is(Number.FromText([NEQ]), type number) then ">100" else "") otherwise
if [NEQ]="NAS" then "Some String" else
if [NEQ] = "" then "Empty String" else
"Other String"

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlWK1YlWMjSCUAaGYNrPMRhMg4nElLQUpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NEQ = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try (if Number.FromText([NEQ])<=10 then "<=10" else
if Number.FromText([NEQ])<=100 then "<=100" else
if Value.Is(Number.FromText([NEQ]), type number) then ">100" else "") otherwise
if [NEQ]="NAS" then "Some String" else
if [NEQ] = "" then "Empty String" else
"Other String")
in
    #"Added Custom"

[DanFromMontreal · Accepted solution]

@Vijay_A_Verma , thank you, it does the trick.

First time I see a "otherwise" in an "if" statement.  Researched it but could not find anything.

Always thought that the structure is pretty linear, meaning that if it meets the FIRST criteria, it exit with the "then".

If not, it looks at the SECOND (else if). criteria and so fourth...

If none of the X previous criteria was meet then it returns the final else "value by default".

Why do we need the "otherwise"??? 

[Vijay_A_Verma]

This is try otherwise statement - This is to handle error. (Error handling is described here - https://docs.microsoft.com/en-us/powerquery-m/m-spec-error-handling  )

Since I am using Number.FromText and if you field is not number, it will generate error. It will mean that this field is text. Hence due to otherwise, it will reach to following statement

if [NEQ]="NAS" then "Some String" else