Forum Discussion
Dealing with various data formats in the same column
Hello,
My data includes both numbers and texts in the same column which I get from an excel file. Power BI recognizes data type of this column as "Text" since I guess it has both numbers and texts inside.
Data Desired Format
12.23232414 Number
0.121000000 Percent
abcdefg Text
What I am trying to do is converting numbers to my desired format and leave texts as they are. I tried to have a calculated column with FORMAT function like:
Data Formatted = IF([Desired Format] = "Percent", FORMAT([Data],"Percent") , IF([Desired Format] = "Number", FORMAT([Data],"Standard"), [Data]))
in order to have:
Data Desired Format Data Formatted
12.23232414 Number 12.23
0.121000000 Percent 12.10%
abcdefg Text abcdefg
But it does not work.
Thanks in advance for your help.
3 Replies
- amitchandak
Super User
Anonymous , You have try like
Switch( True(),
[Desired Format] = "Percent",FORMAT([Data],"0.00%")
[Desired Format] = "Number",FORMAT([Data],"##,##0.00")
//other
)Formats - refer here https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications
amitchandak The problem is FORMAT function does not work for me. I tried without any IF or SWITCH , just
Data Formatted=FORMAT([Data],"0.00%")
But I still get the same format as original, like:
Data Desired Format Data Formatted
12.23232414 Number 12.23232414
0.121000000 Percent 0.121000000
abcdefg Text abcdefg
