Forum Discussion
Adding custom column with If function
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.
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.
7 Replies
You cannot apply less than/greater than operators on a string/text datatype. Change the Recence column to a numeric datatype.
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
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.
