Adding custom column with If function

[olitetu]

I'm trying to add a column returning values depending on which range another column's value fits in.

I tried the following:

= Table.AddColumn(#"Renamed Columns", "Recence Groupe",
each if [Recence] < "30" then "5"
else if [Recence] < "90" then "4"
else if [Recence] < "180" then "3"
else if [Recence] < "270" then "2"
else if [Recence] < "360" then "1"
else "0")

But the values returned are only 5 and 4. All the ones with either 3, 2 , 1 automatically fall to 0.

[edhans]

You should comparing values. When Power Query does a string comparison, "3" is less than "4", but "300" is also less than "4" because "300" will sort before "4" in ascending order. I believe it is looking at the ASCII values of the characters.

You need to either convert everything to numbers or use Number.From() around both [Recence] and your strings you are comparing.

[olitetu]

I changed the data type to "Whole Numbers" but now get errors everywhere (before the "4" and "5" were displaying. 

= Table.AddColumn(#"Renamed Columns", "Recence Groupe",
each if [Recence] < 30 then "5"
else if [Recence] < 90 then "4"
else if [Recence] < 180 then "3"
else if [Recence] < 270 then "2" 
else if [Recence] < 360 then "1" 
else "0"))

[edhans · Accepted solution]

is the [Recence] field a string? if so, it should say 

if Number.From([Recence]) < 30, etc.

You cannot compare numbers to text. You will get errors. I suspect it is because before you were getting no errors, so [Recence] must be text as well.

Additionally, if [Recence] should be a number all the way round, just convert the field to a number. In the upper left of the column it probalby has ABC for text. Click on that and convert it to either a decimal number or whole number. Then you don't have to use Number.From() around it. Do that very early in your Power Query steps, before adding any custom columns.

[olitetu]

= Table.AddColumn(#"Replaced Value26", "_RecenceG",
each if Number.From[Recence] < 30 then 5
else if Number.From[Recence] < 90 then 4
else if Number.From[Recence] < 180 then 3
else if Number.From[Recence] < 270 then 2
else if Number.From[Recence] < 360 then 1
else 0)

I tried this syntax and still get errors. 

The column Recence was changed to values but the custom column switches it back to text..

Sorry I don't understand what is going on. 

[edhans]

You are missing the brackets

= Table.AddColumn(#"Replaced Value26", "_RecenceG",
each if Number.From([Recence]) < 30 then 5
else if Number.From([Recence]) < 90 then 4
else if Number.From([Recence]) < 180 then 3
else if Number.From([Recence]) < 270 then 2
else if Number.From([Recence]) < 360 then 1
else 0)

[HotChilli]

Putting quotes round the values will make them get treated as text. You'll get some unusual behaviour. You want to treat those values as numbers

[StephenK]

You cannot apply less than/greater than operators on a string/text datatype. Change the Recence column to a numeric datatype.