KQL how to find matching values in query and the original value based on an item in the same row as the matched value

user_19393776 0
Viewed 156

So I have a table similar to this

WorkItemId_d Title_s Parent
157 Coke 389
389 Pepsi 157

I need to somehow scan WorkItemId_d and find any values that match Parent, and replace the value in parent with the title of the matching WorkItemId_d to get a table output like this.

WorkItemId_d Title_s Parent
157 Coke Pepsi
389 Pepsi Coke

So the top table is what I have now and the bottom is the end goal. I need to do this without having to manually input 50,000 lines of data. So somehow lookup a value in WorkItemId_d, match it to the number in parent. Then the title that correlates to WorkItemId_d needs to replace the numbers in Parent if that makes sense.

grafana kql azure-devops azure
1 Answers

you could use the join operator: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/joinoperator

for example:

let T = datatable(WorkItemId_d:int, Title_s:string, Parent:int)
[
    157, "Coke", 389,
    389, "Pepsi", 157,
];
T
| join kind=leftouter T on $left.WorkItemId_d == $right.Parent
| project WorkItemId_d, Title_s, Parent = Title_s1
WorkItemId_d Title_s Parent
389 Pepsi Coke
157 Coke Pepsi
user_78618070
Related