getting one extra blank row in the data when using count

[lawada]

i have a table that shows : laundry name, order id, and rating id . for one laundry I'm getting one extra blank row for the rating , for example:
one laundry has total orders =6 and total ratings =1 but i=in power bi its counting it as 6 orders and 2 ratings with one extra blank row for the rating

note that when i run the query in my database i get the count of rating as 1 but in power bi only ot's  adding one extra row

[kate_kk]

May I know any findings for this problem?
Case 1:
I have the same issue, checked there are only 41 rows in data, but after adding a relationship, 
there is an extra row shown in Count() function,
It also shows a blank option in the filter pane with no record.

Case 2: 
After applying and loading data in power query to power bi report, 

both the report and data view show an extra blank record.

but there is no record found in the data source and power query editor (filtered with null, result = 0)
and no relationship was applied in the report.
Checked another report with the same data source but no extra blank row problem.

[aj1973]

Hi @lawada 

You can go to the filter panel and deselect the blank for that rating_id field

[lawada]

that's not possible if i want to count values, it will count the blank row however theres no blank value in the data

[bcdobbs]

Hi,

A few questions...

1) Have you written a measure to do the count or are just dropping the column into the visual ? If it's a measure can you share? If you're dropping column into visual I'm assuming you've checked what aggregation it is set to use? (Will normally default to Sum rather than count).

2) Is it possible a row looks blank but actually has something like a space in it?

3) Is it all just a single table or do you have relationships involved? If you do can you share a picture of your data model or even better the pbix file (or demo version of it)

Ben

[lawada]

1)its just measure with count function

2) i checked in the database and there's one value

3)theres no relationship with other tables and .

[bcdobbs]

Do you want to strip any sensitive data out and send your pbix file. Nothing in your answers is jumping out as the cause! Will happily take a look.

[bcdobbs]

The other thing to try is when you import in power query run Clean and then Trim on the column in question. Fairly sure there might be some hidden blank space. Worth ruling it out anyway.

[lawada]

using count instead of distinct count worked for me . distinct count considers the blank row however count doeant. will try to find out if theres any blank data also

[AlexisOlson · Accepted solution]

FYI, there's also a function DISTINCTCOUNTNOBLANK.