Forum Discussion

randyrettig's avatar
randyrettig
Helper I
2 years ago

Ignoring Text Values

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
        )
    )
Need Help

5 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    2 years ago

    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's avatar
      randyrettig
      Helper I
      2 years ago

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

       

       

      • vanessafvg's avatar
        vanessafvg
        Community Champion
        2 years ago

        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