Power BI not recognising numerical values

[anandmarsh]

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 aggregate or sum the values to allow conditional formatting in PBI)

I can do a count on the column which DOES allow conditional formatting - but this is not what i want. 

The funny thing is that I did the same exercise on another PBI file with a modiifed excel sheet and it worked fine.

[FabioPM]

Hi there!

Someone have found solution about this topic?
I'm using the Version: 2.122.746.0 64-bit (Out/2023).

Power BI does not recognize excel column values with condition.
Excel and PBI were defined as number (decimal)

If you use the IFS conditional, it works well, but if you use IF conditional, PBI does not read the numeric values, it recognizes them as boolean (true or false | 0 or -1).

Some suggestion?
Thanks

[Anonymous]

Has anyone managed to solve this issue?

In my case I'm using amazon redshift has a data source. Wasn't having any trouble until a couple of days ago. Same issue; a column that is clearly formatted as numeric is later recognized as text.

[anandmarsh]

Dont worry - Power BI is being buggy - sorted.

Power BI team, if you want to look into bug - chronology:

Uploaded excel file (4 text cols + 1 numerical col)

Conditional formatting wouldnt work (numerical column not recognised as numbers - could only conditional format if the count option was on)

I noticed that the uploaded file for some reason had an additional blank row loaded into dataset.

(*) Deleted blank row, saved excel, refreshed Power BI - numerical column still not recognised as numbers (blank row had disappeared).

(#) Started new Power Bi file, imported excel, numerical column recognised as numbers, conditional formatting works.

Between (*) and (#) i did not do any changes to excel file. Maybe in the refresh stage Power Bi failed to reconise change. 

[v-xjiin-msft]

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]

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]

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]

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? 

[RC8]

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?

[anandmarsh]

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)