If statement - Expression.Error: We cannot convert the value to type text

[pmc086]

Trying to do what I figured was a simple nested if statement in power query. Use the 'add custom column' function to build this quickly.

The resultant M formula is:

= Table.AddColumn(#"Reordered Columns", "Grade", each if [Grade Original] = "Prep" then 0 else if Text.Start([Grade Original],1) = "7" then 7 else -1)

As you can see in the above, the first evaluation of the if statement works, as does the else if. It's falling over at the else statement which I thought would be pretty simple (I actually want to return the [Original Grade] column as the else but simplified it to debug.

It doesn't matter what value I try and put in the 'else' statement (tried a string, number, null), it throws the same error (below). Can't for the life of me work out what the issue is but it would be great if someone could provide some insight.

If it matters, I'm using power query in Excel and have the latest office 365 release. I've tried searching for similar errors but can't find anything that works.

Thanks in advance.

[Anonymous · Accepted solution]

The datatype for the rows that are failing are numbers so the expression Text.Start([Grade Original],1) is returning the error. Either change the column type to text prior to adding the column or modify the formula as show below:

= Table.AddColumn(#"Reordered Columns", "Grade", each if [Grade Original] = "Prep" then 0 else if Text.Start(Text.From([Grade Original]),1) = "7" then 7 else -1)

[pmc086]

Thank you! Yes that was the solution. I hadn't even looked at that part of the formula as it was evaluating correctly for the condition I wanted. Makes total sense now that I have been enlightened.