Get Top parent of an item by recursive formula

[kain]

Hello, 

I am new to Power B
I have a problem to create a formula. 

I have an agregated list of items coming from 5 lists in sharepoint. 

On these items I have a field called parentItem. This is the id of the parent. 

 My goal is to create a calculated column, that will get the top parent of an item.

formula should be something like this

topParentID = -1

if(current_item.parentid != -1) { // we have a parent
int idTopParent = getParentItemID(current_item)
}
if(topParentID != -1){
column.value = "https://linktosharepointtopparent"


function getParentItemID(item anitem) {
int topparentID = anitem.ID
if(anitem.parentlink != -1) {
Getitem where ID = anitem.parentlink
topparentID = getParentItem(anitem.parentid)
}
return topparentID
}

Thanks a lot for your help

[v-luwang-msft]

Hi @kain ，

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[OwenAuger]

Hi @kain 

Would you be happy to create this column with DAX?

If so, the expression for the column would be relatively simple using PATH functions:

Top Parent = 
PATHITEM ( PATH ( YourTable[Item], YourTable[ParentItem] ), 1 )

The PATH function recursively traces the path from each Item to Parent (producing a path represented by a delimited string), and PATHITEM returns the first item from the path, which would be the Top Parent.

You could also write a traditional recursive function like the one you posted in Power Query.

Some examples of this sort of thing are on these two pages:

https://pivotalbi.com/dynamically-flatten-a-parent-child-hierarchy-using-power-query-m/

https://www.excelnaccess.com/replicating-path-function-of-dax-in-power-query-m/

Regards,

Owen

[kain]

If I can do it in DAX, this is perfect. I'm checking your proposition and mark it as solution if it works. Thanks