Power Query shows null and table visual shows blanks

[Beth_H]

Hi there.

I have a dataset where I have transformed the data into a pivot table such that it looks like the example below:

Id	RecordId	Agency	Location	Extension	Department
1	1	Blue	null	null	null
2	1	null	London	null	null
3	1	null	null	Yes	null
4	2	Red	null	null	null
5	2	null	null	Yes	null
6	3	null	null	null	North
7	3	null	Glasgow	null	null
8	3	null	null	Yes	null
9	4	Orange	null	null	null
10	4	null	London	null	null

In Query, the table shows null values, which is what I want. However when I load to desktop and put the table into a table visual, it looks like PBI is duplicating rows.

EG. When I pull RecordId and Agency into a table visual, it looks like the below:

RecordId	Agency
1
1	Blue
2
2	Red
3
4
4	Orange

I think the issue is that PBI takes the nulls as BLANK values and thus shows each record id with both a null and an entry for Agency as having two inputs. This is confirmed when I look at the table in Table View as the nulls have been replaced by blanks. 

Is there a way to change this?

[Anonymous · Accepted solution]

Hi @Beth_H ,

According to your statement, I think Power BI will show blank when there is no data in text type column.

Then Power BI will only show only once if multiple rows' data are the same. This is by design and is better for us to show different data.

So your visual will look like as:

RecordId	Agency
1
1	Blue
2
2	Red
3
4
4	Orange

It is better for you to add a unique key column to expand your visual, such as [Id] column.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mickey64]

I made a matrix, but there are many 'null's in the matrix.

I use the 'Filters' pane below.

[Beth_H]

Thank you but this isn't quite the issue.

When I pull into the table visual, the table doesn't show nulls, it shows blanks. All nulls have disappeared. So your first matric doesn't look like what I have.

And, if I try your solution and apply the filter 'Agency is not blank', then when I pull in additional fields I get this result.

RecordId	Agency	Location	Extension	Department
1	Blue
2	Red
4	Orange

[mickey64]

I copy your text data in the web page.

These 'null' data are Text-type data in my matrix.

In Excel sheet, when you don't input any data and format some cells, these cells are 'blank' data not  'null' data.

How about your 'null' data?

Don't you format some cells?

For example, border line...