Forum Discussion

Balkrishnan's avatar
Balkrishnan
Helper II
6 years ago
Solved

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 250...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    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's avatar
edhans
Community Champion
6 years ago

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