Using Text.Length in an IF Statement

[MBroman]

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!

[Prasad_chattu]

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"

[PwerQueryKees · Accepted solution]

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

[MBroman]

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. 

[PwerQueryKees]

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