Forum Discussion
Using Text.Length in an IF Statement
Hello, I am trying convert an Excel formula to be used in Power Query. The Excel formula is =IF($K2>0,"Account",IF(LEN($H2)>3,"5 Dig","3 Dig")). From my research I've come up with the following formula in Power Query, =if [Account] <> 0 then "Account" else if Text.Length([ZIP CODE] >3 then "5 Dig" else "3 Dig".
However I keep getting an error. Expression.Error: We cannot convert the value 7 to type Text. Details: Value=7, Type=[Type].
I appreciate any assistance!
I am pretty sure the M Code you gave is not the actual M code giving the error, because ther is a syntax error in it. No closing ) for the Text.Length()
Please post (screenshots of) your data, some or all of the surrounding M Code and a screenshot of the error. By the look of it, the error might occur somewhere else...
4 Replies
I am pretty sure the M Code you gave is not the actual M code giving the error, because ther is a syntax error in it. No closing ) for the Text.Length()
Please post (screenshots of) your data, some or all of the surrounding M Code and a screenshot of the error. By the look of it, the error might occur somewhere else...
Thank you. The missing ) was my error when typing out the formula, the original did have the closing ). However I did go back to review the previous step and found that the Changed Type step removed the leading zeros of the Zip Codes. Once I removed the Changed Type step the formula was able to correctly populate.
Glad you found the cause. The change type changed the ZIP from a text field into a number field and that caused your error. The removal of the zeroes is a side effect of that.
Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.Kees Stolker
A big fan of Power Query and Excel
Hello MBroman
Please try the below M Code:
if [Account] <> 0 then "Account"
else if Text.Length(Text.From([ZIP CODE])) > 3 then "5 Dig"
else "3 Dig"
