custom column in M query

[majdkaid22]

Hi guys, 

Am using the below formula to filter a particular column based on the last character in the group name 

if Text.End( Text.Upper( [Group] ), "S" )
then "A"
else "B"

it's returning with error

(Expression.Error: We cannot convert the value "S" to type Number.

Details:
Value=S
Type=Type

What does need to be fixed?

Thanks,

M

[ImkeF · Accepted solution]

try this:

if Text.End( Text.Upper( [Group] ), 1) = "S"
then "A"
else "B"

[majdkaid22]

thanks @ImkeF 

for me personal knowledge, why the ,1 made the difference? the goup column is text, hence I thought what I did was right

Thanks mate

Majd

[ImkeF]

This is a syntax-error in the Text.End-formula (second argument need to be the number of letters to be returned - LinkToRessource) as well as in the conditional statment (when you use an if you need to combine it with an operator/comparer - LinkToRessource).

[KHorseman]

Text.End will return the last n characters of a string. You have to tell it how many you want. If you leave off that number it will give you an error because you didn't finish writing the entire formula. Text.End( Text.Upper( [Group] ), 1) returns the last character. Text.End( Text.Upper( [Group] ), 2) would return the last 2 characters, Text.End( Text.Upper( [Group] ), 7) the last 7 characters, etc.