Values not showing in data view

[Anonymous]

Hi,

im currently facing a problem with Power BI that is a little bit unnerving. I'm importing data from a SharePoint hosted Excel file and everything works very well, except for one column in the Excel file that is not correctly represented in PBI. 

The column in question contains blank fields and field with a "1".  In the query editor, everything looks fine:

https://imgur.com/mSyQ7Th (showing a couple of 910 rows, formatted as "whole number")

In the data view however, things look a bit differently:

https://imgur.com/kAM6bdS

Clicking on the filter of this column, the data is also shown correctly (ones and blanks):

https://imgur.com/GK9scMA

Problem is: The data does not behave correctly in the visual section as well. I can not filter data with value 1 or blank, but this is a vital function of my reporting sheet. 

What I tried to do to solve this problem (neither approach has worked).

• Format the column in PBI as text and binary (true / false) 
• Replace the blank fields with zeros (in PBI, in combination with first point)
• Change the logic in the Excel table to return "Y" and "N" instead of 1 and blank
• Import the data into a new PBI file

Is this a known problem and does anybody have a solution for this?

Thank you and best regards, David

[v-yuezhe-msft]

@Anonymous,

What is your Power BI Desktop version? How about you download the file and directly connect to the Excel file in Power BI Desktop using Excel connector? Could you please share the Excel file which I can reproduce via Private message ?

Regards,
Lydia

[vanessafvg · Accepted solution]

@Anonymous as far as i know this isn't a known problem.

are you sure you not filtering anything out?

so you dont get any errors when you process the field?

if you do a count in your dataview are all 910 rows there?

maybe try creating a custom column  and make it = to that column and see what happens

my feeling is something is filtering it out in your visual, try creating a table in your visual pane and add all the rows into it and see if they = 910

[Anonymous]

Hello,

thank you for your suggestion about the filtering. That was the problem. I set a filter that was supposed to be visual specific but I activated it so that it filters the whole page. 

The wrong displaying of the column values in the data view remains, though. But since I can filter my data now in the visuals section as desired, it's not a problem anymore.

Thank you again.

Best regards, David