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

Question

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.

Answer 1

Here is one apporach:

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"]
| mv-expand Token =Tokens to typeof(string)
| summarize count() by Token, Shop
| order by Token asc

Here is the output: