Forum Discussion
Adding custom column with If function
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.
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.
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"))
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.
= 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.
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)