cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors