DataFormat Error: We Couldn't convert to Number

[Anonymous]

Hi,

I am getting this error when I try to sort a True/False Column in Descending order

[DataFormat.Error] We couldn't convert to Number.
Details:
   O

When I filter the field, all I see is True/False on the field.

Thanks

-w

[HotChilli]

Can you provide some data that does not show an error when the 4th character is not a number (so a text value that does not error)? 

I would expect the Number.From to throw an error for any text value that won't convert to number.

There are ways to deal with this but maybe you can provide the example first.

[Anonymous]

Thanks Chilli,

I can't -- it is proprietary. I rolled through all the other rows that are false.
In each instance, the 4th char is a space. So it seems if the 4th character is a space, the formula is safe to use as is.

Thanks

-w

[Anonymous]

Thanks Amichandak,

I found this good article on DataFormat Error here.
I started following the steps
I see what is causing a problem where a string has an O (not a zero(0)) in the 4th position
What I do not understand:
I think my Power Query Inspection formula should handle the string and simply return a true or false -- not an error.

Formula:
Value.Is(Number.From(Text.Range([myfield],3,1)),type number))

I am only getting the 1 error, so the formula is working for all other instances where sometimes the 4th character is a number, sometimes not.

Thanks

-w

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can refer the following link to update the formula of custom column as below:

Error handling

=try Value.Is(Number.From(Text.Range([myfield],3,1)),type number) otherwise false

Best Regards

[Anonymous]

Thanks Rena - perfect!

Thanks,

-w

[amitchandak]

@Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.