Forum Discussion
Numbers showing abnormal values in ABC/123 Column Format
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).
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.
3 Replies
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.
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.
Thank you very much for support.
