Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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