Extend a columns value in same table

Question

I have the below datatable, where WId and ParentId are values of the same column but are related to each other. The State that's shown here is for WId, I want to extend another column as ParentIdState which should be the State of ParentId. (The value of State also exists in the same table). How can I do so?

datatable(WId:int, WType:string, Link:string, ParentId:dynamic, State:string)
[
    374075, "Deliverable", "Link", dynamic(315968), "Started", 
]

Updating further for clarification -

datatable(WId:int, WType:string, Link:string, ParentId:dynamic, State:string)
[
    374075, "Deliverable", "Link", dynamic(315968), "Started", 
    315968, "Parent", "Link", dynamic(467145), "Planned"
]

ParentId is dynamic because it's extracted from a JSON. In the above datatable ParentId is actually a value of WId and has its relevant details. My intent is to extend my table to give ParentState in another column like below -

Table

Answer 1

You should use join or lookup.

Answer 2

I believe you could join 2 tables:

1. the one you provided with a small modification - type of ParentId is changed from dynamic to int (the same as the type of WId as the join will be performed on it).
2. a simplified version of table 1) - with only 2 columns: WId and State

let data = datatable(WId:int, WType:string, Link:string, ParentId:dynamic, State:string)
[
    374075, "Deliverable", "Link", dynamic(315968), "Started", 
    315968, "Parent", "Link", dynamic(467145), "Planned"
]
| extend ParentId = toint(ParentId); // to make sure the type of ParentId is the same as WId

data
| join kind=leftouter (data | project WId, State) on $left.ParentId == $right.WId
| project WId, WType, Link, ParentId, State, ParentState = State1

There might be some optimization to be done here (for example by using materialize, but I'm not entirely sure)

You can also achieve the same with lookup

data
| lookup (data | project WId, State) on $left.ParentId == $right.WId
| project WId, WType, Link, ParentId, State, ParentState = State1