Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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
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
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
If I can do it in DAX, this is perfect. I'm checking your proposition and mark it as solution if it works. Thanks
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.