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

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.

CodePudding user response：

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