Column with two data types

[Balkrishnan]

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.

[jdbuchanan71 · Accepted 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] ), "#,#" )
)

[edhans]

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.?

[Balkrishnan]

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)