How to filter distinct values for a kusto column

Eats 0
Viewed 38

I want to keep distinct values for the given column. Assume I have the following columns out of which I want distint values for the column Values. Here is the input

Name       Values      Desc
B          "1,2,1,3"   Sample desc 1
A          "3,1,3"     Sample desc 2
A          "4"         Sample desc 3

This is the output I`m expecting:

Name       Values      Desc
B          "1,2,3"     Sample desc 1
A          "3,1"       Sample desc 2
A          "4"         Sample desc 3

I have this query in place:

Table
| project Name, Values, Desc

Please help with how to modify this kusto query to return the desired output.

azure-data-explorer kql
3 Answers
datatable(Name:string, Values:string, Desc:string)
[
    'B' ,'"1,2,1,3"' ,"Sample desc 1"
   ,'A' ,'"3,1,3"'   ,"Sample desc 2"
   ,'A' ,'"4"'       ,"Sample desc 3"
]    
| extend Values = set_union(parse_json(strcat("[", trim('"', Values), "]")), dynamic([]))
Name Values Desc
B [1,2,3] Sample desc 1
A [3,1] Sample desc 2
A [4] Sample desc 3

Fiddle

David דודו Markovitz0 
datatable(Name:string, Values:string, Desc:string)
[
    'B' ,'"1,2,1,3"' ,"Sample desc 1"
   ,'A' ,'"3,1,3"'   ,"Sample desc 2"
   ,'A' ,'"4"'       ,"Sample desc 3"
]    
| extend Values = strcat_array(set_union(split(trim('"', Values), ","), dynamic([])), ",")
Name Values Desc
B 1,2,3 Sample desc 1
A 3,1 Sample desc 2
A 4 Sample desc 3

Fiddle

David דודו Markovitz0 
datatable(Name:string, Values:string, Desc:string)
[
    'B' ,'"1,2,1,3"' ,"Sample desc 1"
   ,'A' ,'"3,1,3"'   ,"Sample desc 2"
   ,'A' ,'"4"'       ,"Sample desc 3"
]    
| mv-apply num = extract_all(@"(\d+)", Values) on (summarize Values = make_set(toint(num)))
Name Desc Values
B Sample desc 1 [1,2,3]
A Sample desc 2 [3,1]
A Sample desc 3 [4]

Fiddle

David דודו Markovitz0
Related