Forum Discussion
Column with two data types
I have an Excel Data set with a column like below
Rent
4,525
3,750
ABC
4,100
2,500
When i load this data into Powe BI it is displayed without comma like below
Rent
4525
3750
ABC
4100
2500
I tried different formating but could not have data with numeric(with Comma) and text. Please let me know any quick solution.
Hello Balkrishnan
The problem you are running into is excel does not actually store the comma. 4,525 is stored as 4525 and formatted with the comma. We can add a column to your table to format the numbers correctly though.
Rent Formatted = IF ( ISERROR ( VALUE ( Sheet1[Rent] ) ), Sheet1[Rent], FORMAT ( VALUE ( Sheet1[Rent] ), "#,#" ) )
3 Replies
Hello Balkrishnan
The problem you are running into is excel does not actually store the comma. 4,525 is stored as 4525 and formatted with the comma. We can add a column to your table to format the numbers correctly though.
Rent Formatted = IF ( ISERROR ( VALUE ( Sheet1[Rent] ) ), Sheet1[Rent], FORMAT ( VALUE ( Sheet1[Rent] ), "#,#" ) )You are going to have to split it into different columns. Power BI cannot have multiple data types in a column. It put that entire column as text. You can use the FORMAT() function to change how the numbers look, but they will still be text.
Without knowing the context of that data, I cannot provide a sample. I don't know what the ABC represents. It is a column heading, is every 3rd piece of data alpha numeric, etc.?
ABC is some dummy text i entered. My goal is to have comma in Numeric cell(4,350) and not to touch ABC(Some text Value)
