news and know-how about microsoft, technology, cloud and more.

How to filter entries with Null value in Azure Table Storage Explorer

Azure storage accounts are a great and inexpensive way to store lots of data. I am using Azure Table storage to store large amounts of structured data for various tools. I needed to filter all rows that have a specific field that shows Null. While this sounds simple, it is a bit tricky. Here's how.

The problem is that columns showing "Null" just don't exist, but they are shown in list with Null. Therefore, it does not work to filter them out. In my example, I had a table with a column called Users (of type Int32) that contained items with a number and others that had no value. So, the following query filters for a timeframe and all items having a Users value greater than 0. Unfortunately, the query also shows elements with the Null value...


The solution is to manually edit the query to negate the condition in the Text editor (the second icon in the query menu) by adding a not() function to the Users condition.

Timestamp ge datetime'2021-06-01T15:42:21.000Z' 
and Timestamp lt datetime'2021-12-31T16:42:21.000Z'
and not(Users gt 0)

This query returns a result showing all items in the timeframe that have no value for column Users set, as here.


Then we can work with the filtered data, like exporting, deleting or other further processing of the data.

A simple workaround for getting data where there are values missing. Thanks to my colleague Christoph Wilfing for the support!