Replacing empty string column with null in Kusto

Question

How do I replace empty (non null) column of string datatype with null value?

So say the following query returns non zero recordset:-

mytable | where mycol == ""

Now these are the rows with mycol containing empty strings. I want to replace these with nulls. Now, from what I have read in the kusto documentation we have datatype specific null literals such as int(null),datetime(null),guid(null) etc. But there is no string(null). The closest to string is guid, but when I use it in the following manner, I get an error:-

mytable | where mycol == "" | extend test = translate(mycol,guid(null))

The error:-

translate(): argument #0 must be string literal

So what is the way out then?

Update:-

datatable(n:int,s:string)
[
    10,"hello",
    10,"",
    11,"world",
    11,"",
    12,""
]
| summarize myset=make_set(s) by n

If you execute this, you can see that empty strings are being considered as part of sets. I don't want this, no such empty strings should be part of my array. But at the same time I don't want to lose value of n, and this is exactly what will happen if I if I use isnotempty function. So in the following example, you can see that the row where n=12 is not returned, there is no need to skip n=12, one could always get an empty array:-

datatable(n:int,s:string)
[
    10,"hello",
    10,"",
    11,"world",
    11,"",
    12,""
]
| where isnotempty(s)
| summarize myset=make_set(s) by n

Yoni L. Yoni L. · Accepted Answer · 2019-03-27T01:00:11

There's currently no support for null values for the string datatype: https://docs.microsoft.com/en-us/azure/kusto/query/scalar-data-types/null-values

I'm pretty certain that in itself, that shouldn't block you from reaching your end goal, but that goal isn't currently clear.

[update based on your update:]

datatable(n:int,s:string)
[
    10,"hello",
    10,"",
    11,"world",
    11,"",
    12,""
]
| summarize make_set(todynamic(s)) by n

Replacing empty string column with null in Kusto

1 Answers