Error based on a column

[powerbidev123]

Can anyone please help as to how to resolve this error. The value is also a text which is showing error and also I have changed the data type of the column to text yet I am getting this data format error.

[powerbidev123 · Accepted solution]

@smokeyjoe03 , but i need to keep  that "Less than year" value in my column along with the numerical values . How can I go about it?

[smokeyjoe03 · Accepted solution]

The column will have to remain as a string in that case, the "Change Type" operation operates on every value in the column so even just 1 value that isn't a number or NULL will cause the error.

As a workaround I suggest making two columns:

1. A Number column but replace your "Less than a Year" with a 0. This will allow you to use this column to do mathematical operations still.
2. A String column that keeps the numbers as strings and allows the value "Less than a Year". This can be used for display purposes.

It would look something like this:

YearsColumn (Number)	DisplayColumn (Text)
0	Less than a year
1	1
5	5

[Omid_Motamedise]

What formula do you used?

It mention you are going to compare date and number which is impossible, instead of using number like 2, use #duration(2,0,0,0)

[smokeyjoe03]

In the "Details" in the error it suggests the value you are trying to change to a number is the string "Less than a Year"

Anything in that column that isn't a numerical value will cause the error when converting it.

[powerbidev123 · Accepted solution]

@smokeyjoe03 , but i need to keep  that "Less than year" value in my column along with the numerical values . How can I go about it?

[smokeyjoe03 · Accepted solution]

The column will have to remain as a string in that case, the "Change Type" operation operates on every value in the column so even just 1 value that isn't a number or NULL will cause the error.

As a workaround I suggest making two columns:

1. A Number column but replace your "Less than a Year" with a 0. This will allow you to use this column to do mathematical operations still.
2. A String column that keeps the numbers as strings and allows the value "Less than a Year". This can be used for display purposes.

It would look something like this:

YearsColumn (Number)	DisplayColumn (Text)
0	Less than a year
1	1
5	5

[powerbidev123 · Accepted solution]

Thanks for the solution @smokeyjoe03