Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
afmcjarre
Helper I
Helper I

Error Handling for Bad Data Using Text.Length

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

 
 
 

2021-03-17_10h02_13.png

 
 

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!

 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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])

 

View solution in original post

2 REPLIES 2
afmcjarre
Helper I
Helper I

Thank you very much!

AlexisOlson
Super User
Super User

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])

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.