Forum Discussion

anandmarsh's avatar
anandmarsh
Regular Visitor
8 years ago

Power BI not recognising numerical values

Hi All,   I have displayed some data from the excel file into a table in Power BI.    4 columns are text 1 column is nummerical   Power BI doesnt recognise the numerical values (i.e. i cannot ...
v-xjiin-msft's avatar
v-xjiin-msft
Solution Sage
8 years ago

Hi anandmarsh,

 

I have made some test based on your chronology. However I cannot repro your issue. By the way I'm using Version: 2.56.5023.1021 64-bit (March 2018) Power BI Desktop. 

 

What's your Power BI Desktop version? You can check it at File -> Help -> About.

 

Also, please share us a sample Excel file which can help us repro your issue.

 

Thanks,
Xi Jin.

anandmarsh's avatar
anandmarsh
Regular Visitor
8 years ago

I am using Version 2.56.5023.1021 64-bit (March 2018).

 

The problem arises when the data is imported. It wont recognise values (even though they are recognised in excel as values)

 

Refer images - ignore blue squares - I have to block out data (confidential). 

1) excel will filter values in ascending/descending (it recognises values)

2) power bi says that the values are "abc123"

3) I have to "Change Type" to numeric

4) power bi says that the values are "123"

 

 

1)

 

 

2)

 

 

 

 

 

3)

 

 

 

 

4)

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    Hi anandmarsh,

     

    What's the data type of this column in Excel file? According to your first screenshot, it seems like it is a General type.  

     

    You should know that in Power BI, there's no General type column. So for the first time you load your excel into Power BI. It will define this column as text type since it is General in Excel.

     

    Then once you created a dataset in Power BI, the data type of each column would be defined and these column types will not be changed automatically. It also means that even you refresh both the Excel file and the dataset, the column type is still text. You have to "Change Type" manually.

     

    Or change the column type to numeric at the begining in Excel file. And create a new Power BI report.

     

    Thanks,
    Xi Jin.

    • RC8's avatar
      RC8
      Regular Visitor
      8 years ago

      I am experiencing a similar issue but the symptoms are a bit different.

       

      If I create a matrix with numeric values I can access the conditional formatting features.  However, if I wrap the values in the FORMAT function and use "Percent" as the format, the conditional formatting options disappear as if Power BI no longer believes these are numeric values.  Is anyone else experiencing this issue?

       

       

    • RC8's avatar
      RC8
      Regular Visitor
      8 years ago

      I have found another situation where Power BI does not recognize numeric values. 

       

      I used the FORMAT function to display the digits as PERCENT.  As soon as that is applied, Power BI seems to believe the values are now strings and the conditional formatting options disappear.  I'm using the February 2018 release version 2.55.5010.641 64-bit.  Anyone else experience the same thing?