Forum Discussion

Balkrishnan's avatar
Balkrishnan
Icon for Helper II rankHelper 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] ), "#,#" )
)

     

     

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
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] ), "#,#" )
)