Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.