CODEWITHSUNDEEP
sankey-diagramazure-data-explorerkql
Rishav Sharan
Rishav Sharan

Reputation: 2932

Transforming Kusto array into specific tabular format

I have my data in the format

enter image description here

I need to transform the data into [source, destination and count] format

enter image description here

so that I can create a sankey chart out of it. Can I do any transform like this in Kusto itself or is this possible only via a programming language? If it can be done in kusto itself, can you point the way please.

Upvotes: 1

Views: 1396

Answers (1)

RoyO
RoyO

Reputation: 271

Transforming a dynamic array is possible using mv-apply operator, then you could use prev() function to get the previous row's value in order to generate the From column:

datatable (IdCol:long, Ordered_States_List:dynamic )
[1,dynamic(["State01","State02","State05"]),
2,dynamic(["State02","State03","State05"]),
3,dynamic(["State01","State04"]),
4,dynamic(["State01","State02","State03"])]
| mv-apply Ordered_States_List to typeof(string) on 
(
    project From = prev(Ordered_States_List), To=Ordered_States_List
)
| where isnotempty(From)
| summarize value=count() by From, To

Upvotes: 3

Related Questions