Unable to Group All Values

[Anonymous]

This is my first post here. I've used Power BI for over a year for my agency's reporting. This is the first time I've run into this issue, though.

I have a set of data with a variety of demographics from persons who have contacted us. I have several categories that I want to group values, however, it seems to be unable to include all of the values. For example, I have ages of contacts. I've grouped these into categories & would like the blanks to be grouped into a category with those who have refused to provide age or those we did not ask. I've done this on numerous occasions in other reports, but in this dataset, it doesn't seem to be doing it.

I've checked my settings. All of the ones with blank ages, refused, or did not ask are grouped into the Other category as values not grouped. Yet, when I display these in a graph, the blank ones still show up in a category of their own. I can provide screenshots to show what I am seeing if it would help. I've tried everything with nothing working. 

This is only one category where I am having this issue. I have at least 4 others where this same phenomenon is occurring. I feel like the same solution would be applied to the other groups & I would be able to fix all of them with the same solution.

The data is contained in rows with 1 row = 1 contact, each with their own distinct contact value. There are approximately 1.7 million rows divided into various CSV files in a single folder. 

[Anonymous]

Ok...let's see if I can add these screenshots. The first one shows the settings for the group. The next one shows the resulting visual after the grouping takes effect. You can see that it still contains the blank category, even though I've indicated it should be grouped as an "Other" category.

[camargos88]

@Anonymous ,

Have you tried to create a column for it ?

[Anonymous]

No, I haven't.

Each of these values resides in the same column in the dataset called "Age." This problem occurs in several categories of data: gender, military affiliation, method of contact with our agency. 

The dataset has 1 row for each contact. For example:

Call ID

Date

Employee

Age

Gender

Military Affiliation

Contact Method

Disposition of contact

Zip Code

The call report number in the dataset is the unique identifier & links to other portions of the dashboard in a separate table. This dataset has 26 separate CSV files that make up the full scope of contacts for a single year. The blank values make up about 15% of the dataset in any one of the categories.

I have a similar dashboard that has about 400,000 records in it (so far) and I grouped the data the same way. That dashboard doesn't have this issue. The blank values group together as the settings would indicate. I'm at a loss to understand why the problem only occurs in this particular dataset & not in the other one. The makeup of the data in terms of structure is exactly the same.

[camargos88 · Accepted solution]

@Anonymous,

I tried to replicate it with dummy data and it's the same, I got a bar with blank values.

Does your source have null or empty string / filled field ? Maybe this is the difference between them.

Also you can replace this value by a value for missing.

[Laxman007]

i got the similer issue, still not able to find how values going to other category

[Anonymous]

I found the issue. One of the CSV files didn't have the columns in the same order as the others. I'm not sure how it happened because the export process from our software hasn't changed. Doesn't matter. I found out that the column it was looking at in one of the files was filled with null values for each of the areas I was noticing this issue.

Thank you for suggesting the null values. I don't think I would have taken a closer look at the data if you hadn't suggested it.

[v-deddai1-msft]

Hi @Anonymous ,

Thank you for sharing the solution. please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[camargos88]

Hi @Anonymous ,

It would be nice having some examples of what/how you are doing.

[Anonymous]

If someone can tell me how I can share a screenshot, I'll be happy to demonstrate what I am seeing. I've looked through the options & can seem to find it. 

My data isn't published online - we are still trying to evaluate how best to display the data outside of our organization.

[camargos88]

@Anonymous ,

When you create a post, you see a camera icon, just upload the picture using it.