How to select and drop empty rows in ADX (KQL)

Question

I can not find any documentation on how to view and drop fully empty rows from an ADX source table.

Using where field == "" doesn't return what we are looking for, and the microsoft docs don't provide much insight. Does anyone know any ways to maybe filter these rows out of the ingestion source table in the first place, or run a cleaning function to automatically drop these rows?

Thanks in advance!

Answer 1

Prefer filter during ingestion over delete.

Demo

.create table t(Id:int, txt:string, val:real)

.ingest inline into table t <|
1,Hello,2.3
,,
,,
2,World,4.5
3,,null
,,

t

Id	txt	val
1	Hello	2.3
2	World	4.5
3

Naive checking

t
| where not
        (
                isnull(Id)
            and isempty(txt) // string is never null 
            and isnull(val)
        )

or

t 
| where     isnotnull(Id)
        or  isnotempty(txt)
        or  isnotnull(val)

Shortcut

t
| where tostring(pack_all(true)) != "{}"

Id	txt	val
1	Hello	2.3
2	World	4.5
3