kusto - How to add row if doesnt exists in result table from kusto query?

Question

Is there any way, how to add row/rows if not exists in the result table from kusto query ? I need to create report from application insights and I must to get table with next format:

name_column	count
X	58
Y	9
Z	15

X, Y, Z I get from app logs but not every time X, Y, Z are in logs. Sometimes Z or Y or X are dont exists in logs and I get table, for example:

name_column	count
X	58
Y	9

I need add row with name_column "Z" and with count "0" and my correct result table should be:

name_column	count
X	58
Y	9
Z	0

Can someone please help me ?

Answer 1

let YourQueryResults = datatable(name_column:string, count:int)["X", 58, "Y", 9];
let NameColumn = datatable(name_column:string)["X", "Y", "Z"];
NameColumn
| lookup YourQueryResults on name_column 
| extend count = coalesce(['count'], 0)

name_column	count
X	58
Y	9
Z	0

Fiddle