Power Query Custom Column with Numbers and Text

Question

I am trying to clean up my data by converting certain values to a number "2" but need to leave remaining data and data type as is. I am using the following code with a custom column step in power query (excel) but it is giving me an error when returning a number.

Number.From([Values_old]) otherwise if Text.Contains([Value_old],"not required",Comparer.OrdinalIgnoreCase) or Text.Lower([Value_old]) = "N/A" or Text.Lower([Value_old]) ="NA" or [Value_old] = "100" or [Value_old] = 100 then 2 else [Value_old]

See the result from the step:

I based my conditional column from the following comment I found in a forum: https://community.powerbi.com/t5/Desktop/data-type-which-contains-both-test-and-number/m-p/55785/highlight/true#M22664

However, this seems to break my if then else condition as well.

Answer 1

It's simple. you used the wrong column name, Values_old instead of Value_old

But your formula won't work then either since neither of these will ever work:

Text.Lower([Value_old]) = "N/A" or Text.Lower([Value_old]) ="NA"

because you are comparing something you just converted to lower case against an upper case

so you probably want below, which includes the try part you seem to have left out of your code

= Table.AddColumn(#"Changed Type", "Custom", each try Number.From([Value_old]) otherwise if Text.Contains([Value_old],"not required",Comparer.OrdinalIgnoreCase) or Text.Lower([Value_old]) = "n/a" or Text.Lower([Value_old]) ="na" or [Value_old] = "100" or [Value_old] = 100 then 2 else [Value_old])