CODEWITHSUNDEEP
azure-data-explorerkql
Shivam Raina
Shivam Raina

Reputation: 45

How to separate the unique values from a column in kusto and make new rows for them?

I have a table in Kusto. It looks like this:-

------------------
| Tokens | Shop  |
------------------
| a      |  P    |
| a,b    |  Q    |
| c,d,e  |  P    |
| c      |  R    |
| c,d    |  Q    |
------------------

There are total 12 distinct tokens and tokens column can have any permutation of them(including empty) and the shop column will only have one fixed value out of 5 values possible(can't be empty).

I want to get an output table, having three columns, like this:-

----------------------------------
| Distinct Tokens | Shop | Count |
----------------------------------
| a               | P    |  12   |
| b               | P    |  13   |
| c               | R    |  16   |
| d               | Q    |  2    |
----------------------------------

In short, I want all distinct tokens in one column, and each token mapped with each of the 5 shops available, and count indicating the number of rows in the original table where a specific token came with a specific shop.

Note: count of 'a' with shop 'P' in new table will include the count of rows in original table having 'a' in any of the comma separated values.

I am unable to write a kusto query for this, Please help.

Upvotes: 2

Views: 1382

Answers (1)

Avnera
Avnera

Reputation: 7618

Here is one apporach:

let Example = datatable(Tokens:dynamic, Shop:string)[
    dynamic(["a"]),                "P", 
    dynamic(["a", "b"]),           "Q",
    dynamic(["a", "d", "e"]),      "P",
    dynamic(["c"]),                "R",
    dynamic(["a", "b", "c", "d"]), "Q"
];
//
Example
| mv-expand Token = Tokens to typeof(string)
| summarize count() by Token, Shop
| order by Token asc

Here is the output:

enter image description here

Upvotes: 3

Related Questions