Return rows that only have nulls?

Question

I'm using Kusto/KQL. I have a huge dataset that I am working with that was migrated from another sources. I know that many of the fields only have null values. I'd like to be able to identify those columns. Sort of doing a null check on column by column, is there a way I can find out what columns never have any data?

Answer 1

@John Thoni

Thanks for reaching out to Microsoft Q&A

To identify columns that only contain null values in Kusto Query Language (KQL), you can use the isnull() function to check each column.

Here’s a query that helps you find such columns:

// Replace 'YourTable' with the name of your table
YourTable
| summarize count() by Column1, Column2, Column3, ...
| where isnull(Column1) and isnull(Column2) and isnull(Column3) and ...

This query checks each column for null values and returns rows where all specified columns are null. Adjust the column names as needed for your dataset.

If you want to identify columns that never have any data (i.e., always null), you can use the following approach:

// Replace 'YourTable' with the name of your table
YourTable
| summarize countif(isnull(Column1)) as NullCount1, countif(isnull(Column2)) as NullCount2, ...
| where NullCount1 == count() and NullCount2 == count() and ...

This query counts the number of nulls in each column and compares it to the total row count. If the counts match, it means the column is always null.

Feel free to adjust the column names and table name to fit your specific dataset

Hope this helps. Do let us know if you any further queries.