Transpose ColumnName and Value KQL/Kusto/Data Explorer

user_17322246 0
Viewed 781

I have a dataset that contains many columns with dates. I would like return only the column names and dates then sort by date. In excel, I would accomplish this by transposing the data then sorting. How can I accomplish the same in KQL?

let T1 = datatable (Date1:string, Date2:string, Date3:string, Date4:string, Date5:string, Date6:string, Date7:string, Date8:string, Date9:string, Date10:string, )[
"2021-11-3", "2021-11-4",
"2021-11-5", "2021-11-6",
"2021-11-7", "2021-11-8",
"2021-11-9", "2021-11-10",
"2021-11-11", "2021-11-12"];
T1

Result:

Date1       Date2       Date3       Date4.........   
2021-11-3   2021-11-4   2021-11-5   2021-11-6.....

Desired Result:

DateType  Date
Date1     2021-11-3
Date2     2021-11-4
Date3     2021-11-5
Date4     2021-11-6
...       ...
kql azure-data-explorer
1 Answers

you can use the narrow() plugin: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/narrowplugin

datatable (Date1:string, Date2:string, Date3:string, Date4:string, Date5:string, Date6:string, Date7:string, Date8:string, Date9:string, Date10:string)
[
    "2021-11-3", "2021-11-4", "2021-11-5", "2021-11-6", "2021-11-7", "2021-11-8", "2021-11-9", "2021-11-10", "2021-11-11", "2021-11-12"
]
| evaluate narrow()
| project DateType = Column, Date = Value
DateType Date
Date1 2021-11-3
Date10 2021-11-12
Date2 2021-11-4
Date3 2021-11-5
Date4 2021-11-6
Date5 2021-11-7
Date6 2021-11-8
Date7 2021-11-9
Date8 2021-11-10
Date9 2021-11-11
user_78618070
Related