Forum Discussion
How can I split a variable-depth, slash-delimited path_column field into separate col in KQL?
- 1 year ago
Sure thing - just remove the column defintion fret the pivot function. Then the previous query will be dynamic
Got it 🙂
This KQL query works what you want. Notice the line with the pivot command - here you have the option to add the schema you need. The columns will then be added, even though you don't have a value for that column.
The Pivot them gives you the need to do an aggregation afterwards - so it becomes a bit messy.
I hope this help you. Below code, can be turned into a function if you need it
let T = datatable(row:string, values:string)
[
"row01", "col1/col2/col3",
"row02", "col1/col2/col3/col4",
"row03", "col1/col2",
];
T
| extend ColSplit = split(values, "/")
| mv-expand splittedColumns = ColSplit
| extend SplittedColumnsToString = tostring(splittedColumns)
| evaluate pivot(SplittedColumnsToString, count()) : (row:string, values:string, ColSplit:dynamic , NumberOfColumns:int, splittedColumns:string , col1:string, col2:string,col3:string,col4:string,col5:string,col6:string)
| project-away ColSplit, NumberOfColumns, splittedColumns
| summarize col1 = max(col1), col2 = max(col2), col3 = max(col3), col4 = max(col4), col5 = max(col5), col6 = max(col6) by row, values
- ramankr481 year agoHelper II
I don't think it will work if no of values gets increased in the values column, will it be feasible or change the schema on the fly
- datacoffee1 year agoMost Valuable Professional
Sure thing - just remove the column defintion fret the pivot function. Then the previous query will be dynamic
- ramankr481 year agoHelper II
T
| extend ColSplit = split(values, "/")
| mv-expand ColIndex = range(0, array_length(ColSplit), 1)
| extend columnName = strcat("col", tostring(ColIndex + 1)), columnValue = ColSplit[ColIndex]
| evaluate pivot(columnName, any(columnValue))
| project-away ColSplit, ColIndex