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.
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)