Ignoring Text Values

[randyrettig]

Hi all,

I have this measure to count how many estimates are more than 2 days old by converting the first 5 values of the estimates notes column to a dd/mm date.  The problem is, some of the estimate notes start with text values, or quotation marks, and I can't figure out how to get PBI to ignore those rows.  I've tried going to chat gpt and I'm getting stuck there too.  The below measure returns an error stating Cannot convert value 'T' of type text to type number - this is because it's hitting a note that starts with 'Test Note' instead of a date like 05/14.

EstimatesOlderThan2Days2 =

VAR TodayDate = TODAY()

RETURN

    CALCULATE(

        COUNTROWS('Sheet1'),

        FILTER(

            'Sheet1',

            ISNUMBER(VALUE(LEFT('Sheet1'[estimate Notes], 1))) && -- Check if the first character is a number

            ISNUMBER(DATEVALUE(LEFT('Sheet1'[estimate Notes], 5) & "/" & YEAR(TodayDate))) && -- Check if date extraction is successful

            DATEVALUE(LEFT('Sheet1'[estimate Notes], 5) & "/" & YEAR(TodayDate)) + 2 < TodayDate

        )

    )

[vanessafvg]

so are you saying you dont need to clean those values you just want to ignore them?

otherwise you will have to clean your data first.   you can't convert the data to a number without cleaning it.

what you can do is create a flag in power query (its more performant that way if using import mode) to identify the numbers only

in power query create a custom column

if Value.Is([field_name],type number) 
then 1
else 0

you can then create a measure that allows you to convert to a number by either using this flag in the filter or just create a conditional column in power query to create a new column where the custom column = 1

[randyrettig]

Thank you for the reply.  I did that, but everything came back as 0 in the custom column.

[vanessafvg]

are you able to share your pbix or some sample data?  all those example have note in them it will come back as 0, you need to remove those text notes if you want to use that data

please provide some sample data in text form

[randyrettig]

Unfortunately I cannot.  😞  I appreciate your effort though.

[vanessafvg]

well you can provide sample data that isn't real?

however if you want to use the dates that have text in them

(first tell the source if possible to stop doing that ;))

however you need to replace all the text values with '', if there is a patterns of just a few words its easy to do, just replace values in power query replacing all the different words they use with '', then do a trim, and a clean to remove all the white spaces