Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello All,
I'm working on a dataset with a lot of columns and I want to see for each column how many nulls there are. Also there are some columns that have things like "Unanswered" or "N/A" and I'd want these included in the count as well.
I don't want to have to type in all of the column names as there are over 100 columns to do this across.
Ideally the result would come out like a table like this, but if there's a more appropriate way I'm all ears.
A: 2
B: 4
C: 1
😧 3
Below is what the data looks like
Many Thanks in Advance!
Solved! Go to Solution.
Hi @OhMyDearLord,
You could do something like this
let
n = {null, "Unanswered", "N/A"},
Source = YourTableOrQueryReference,
Result = Table.FromColumns(
{
Table.ColumnNames( Source ),
List.Transform( Table.ToColumns( Source ), each List.Count( List.Select( _, (v) => List.Contains( n, v))) )
}
)
in
Result
Where n contains the items you want to count accross all columns and Source is your table
It will return a table listing all columns in Column1 and the Count in Column2
Hi @OhMyDearLord,
You could do something like this
let
n = {null, "Unanswered", "N/A"},
Source = YourTableOrQueryReference,
Result = Table.FromColumns(
{
Table.ColumnNames( Source ),
List.Transform( Table.ToColumns( Source ), each List.Count( List.Select( _, (v) => List.Contains( n, v))) )
}
)
in
Result
Where n contains the items you want to count accross all columns and Source is your table
It will return a table listing all columns in Column1 and the Count in Column2
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
13 | |
9 | |
7 | |
6 |