Kusto: How to convert columns to rows and summarize by them

Question

I have a table like this: -

Type | shown | eaten  |
-----------------------
a    |  5    |   2    |
b    |  6    |   23   |
c    |  8    |   12   |
d    |  19   |   11   |
e    |  2    |   22   |
-----------------------

I want to convert it like this: -

Type | action | count |
-----------------------
a    | shown  |  5    |
a    | eaten  |  2    |
b    | shown  |  6    |
b    | eaten  |  23   |
c    | shown  |  8    |
c    | eaten  |  12   |
d    | shown  |  19   |
d    | eaten  |  11   |
e    | shown  |  2    |
e    | eaten  |  22   |
-----------------------

How to write a kql query for this?

Answer 1

Here is one option (note that I changed some of the column names to capital case so it does not need escaping).

datatable(Type: string, shown: int, eaten: int) [
"a",  5,  2,
"b",  6, 23,
"c",  8, 12,
"d", 19, 11,
"e",  2, 22]
| extend Temp = pack("shown",shown,"eaten",eaten )
| mv-expand kind=array Temp
| project Type, Action=tostring(Temp[0]), Count=toint(Temp[1])