Obtain highest level parent recursively

Hi psoundararajan,
Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to Shahid12523, wardy912, for those inputs on this thread.

Has your issue been resolved? If the response provided by the community member Shahid12523, wardy912,addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

Hi psoundararajan 

 In power query, add a new blank query and add the following (edit accordingly)

let
    Source = YourTableName,  // Replace with your actual table name
    GetUltimateParents = (item as text) as list =>
        let
            DirectParents = List.SelectRows(Source, each [Item] = item)[Parent],
            UltimateParents = List.Combine(List.Transform(DirectParents, each
                if _ = item then {_} else GetUltimateParents(_)
            ))
        in
            List.Distinct(UltimateParents),

    Result = Table.AddColumn(Source, "UltimateParents", each GetUltimateParents([Item])),
    Expanded = Table.ExpandListColumn(Result, "UltimateParents"),
    Renamed = Table.RenameColumns(Expanded, {{"UltimateParents", "Parent"}})
in
    Renamed

 I hope this helps, please mark as solved if it does, thanks!

Load your Item-Parent table.

Create a function that, given an Item, recursively looks up its Parent until reaching the top (where parent = itself).

Expand results so you keep all combinations.

let
Source = YourTable,

// Recursive function
GetParents = (child as text) as table =>
let
DirectParents = Table.SelectRows(Source, each [Item] = child),
WithRecursive = Table.ExpandTableColumn(
Table.AddColumn(DirectParents, "UltimateParent", each GetParents([Parent])),
"UltimateParent", {"Parent"}
)
in
if Table.IsEmpty(DirectParents)
then #table({"Item","Parent"}, {{child, child}})
else WithRecursive,

// Apply recursion to all Items
DistinctItems = List.Distinct(Source[Item]),
Results = Table.Combine(List.Transform(DistinctItems, each GetParents(_)))
in
Results

psoundararajan 

Hierarchy =
VAR Base = ADDCOLUMNS ( Relations, "OriginalItem", Relations[Item] )
RETURN
GENERATE (
Base,
PATH ( Base[Item], Base[Parent] )
)

try this and let me know if it works 

Hello, thanks for your responses. I'm trying these at the moment. Please allow me some time to get back. 

Hi psoundararajan in power query, add a new blank query > advanced editor, add this, rename it as - GetUltimateParents

(child as text, tbl as table) as table =>
let
// Direct parents of the child
DirectParents = Table.SelectRows(tbl, each [Item] = child),

Result =
if Table.IsEmpty(DirectParents) then
#table({"Item","Parent"}, {})
else
Table.Combine(
List.Transform(
DirectParents[Parent],
(p) =>
if p = child then
// Stop if parent = child (self-loop)
#table({"Item","Parent"}, {{child, p}})
else
let
Sub = @GetUltimateParents(p, tbl),
Output =
if Table.IsEmpty(Sub) then
#table({"Item","Parent"}, {{child, p}})
else
Table.TransformColumns(Sub, {{"Item", each child, type text}})
in
Output
)
)
in
Table.Distinct(Result)

Next create another blank query, add this

let
Source = relations, // replace with your input table’s query name
DistinctItems = List.Distinct(Source[Item]),
Expanded = Table.Combine(
List.Transform(DistinctItems, each GetUltimateParents(_, Source))
)
in
Expanded