Count number of nulls in a column across all columns

[OhMyDearLord]

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!

[OhMyDearLord]

Thanks @m_dekorte this works really well

[m_dekorte · Accepted 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