KQL Text Aggregation

Hi @Manuprasad M N ,

To accomplish you need several Kusto functions:

1. To get the value from the previous row, user the scan operator
2. Combine 2 arrays, use the set_union operator
3. To remove the empty string, use my-apply and make_list_if

This will result in the query below:

datatable (MyData:dynamic )
[dynamic(["Name1","Name2"]),
dynamic([""]),
dynamic(["Name1"]),
dynamic(["Name3"])]
| serialize 
| scan declare (data_combined_and_filtered: dynamic=dynamic(null)) with 
(
    //combine and sort the array
    step s1: true => data_combined_and_filtered = array_sort_asc(set_union(MyData, s1.data_combined_and_filtered));
)
//remove empty strings
| mv-apply data_combined_and_filtered on (
    summarize value = make_list_if(data_combined_and_filtered, isnotempty(data_combined_and_filtered))
)

Hope this will help.

Kind Regards,

Wilko

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".