Forum Discussion
Power Query shows null and table visual shows blanks
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?
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
I made a matrix, but there are many 'null's in the matrix.
I use the 'Filters' pane below.
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 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...
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
