Numbers showing abnormal values in ABC/123 Column Format

[Ahsan_R]

Hi Experts,

My data contains a column "Title" with Text and Numbers in different rows.

While Text is showing complete values, numbers are shown as below. (Covered some data in text rows to keep data private)

Please guide if it can be changed to display as whole number.

If i change column to "Whole Number" it gives error. (As there is text in same column).

[Anonymous · Accepted solution]

Hi @Ahsan_R 

You can refer to the following solution

Sample data 

You can create a calculated column

Column =
VAR a =
    SUMMARIZE (
        ADDCOLUMNS (
            GENERATESERIES ( UNICODE ( "A" ), UNICODE ( "Z" ) ),
            "Character", UNICHAR ( [Value] )
        ),
        [Character]
    )
VAR b =
    COUNTROWS (
        FILTER ( a, CONTAINSSTRING ( EARLIER ( 'Table'[Title] ), [Character] ) )
    )
RETURN
    IF ( ISBLANK ( b ), CONVERT ( [Title], INTEGER ), 0 )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ahsan_R]

Thank you very much for support.

[lbendlin]

You should not use ABC123 (variant)  data type columns, and you should not mix data types in the same column. Worst case define the column as text.