Error Handling for Bad Data Using Text.Length

[afmcjarre]

I am trying to account for bad data in a column that should contain a 4-digit year.

There is an instance where "9" was entered as the year, and I tried to make a custom column to account for this using the following code variations:

= Table.AddColumn(#"Changed Type1", "Custom", each if Text.Length[Year Built] = 4 then [Year Built] else null)

= Table.AddColumn(#"Changed Type1", "Custom", each if Text.Length[Year Built] < 4 then null else  [Year Built])

I keep receiving the following error:

Expression.Error: We cannot apply field access to the type Function.
Details:
    Value=[Function]
    Key=Year Built

Can someone please assist me with this? I made sure to change the "Year Built" column to text data format before using Text.Length but still can't get this to work. This seems like such a simple custom column but I haven't been ablet to figure it out.

Thank you!

[afmcjarre]

Thank you very much!

[AlexisOlson · Accepted solution]

This is a syntax error. You need to use parentheses or else it thinks you are trying to access the field [Year Built] of the function Text.Length, which doesn't exist. Try putting the parentheses in like this:

= Table.AddColumn(#"Changed Type1", "Custom", each if Text.Length([Year Built]) < 4 then null else  [Year Built])