Obtain highest level parent recursively

[psoundararajan]

Hello,

We have a table of relations, and each item has different levels of parent-child relations.

Input data:

Item	Parent
ID1	P1
ID2	P2
ID3	P2
ID4	P3
P1	S1
P1	S2
P2	S2
P3	S3
P3	S4
S1	S1
S2	S2
S3	S3
S4	S4

How do I obtain the following output from above?

Item	Parent
ID1	S1
ID1	S2
ID2	S2
ID3	S2
ID4	S3
ID4	S4
P1	S1
P1	S2
P2	S
P3	S3
P3	S4
S1	S1
S2	S2
S3	S3
S4	S4

An item can be linked to more than one parent. In the target table, I would like to have one row per item-ultimate parent combination. Thanks in advance.

[techies]

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

[v-kpoloju-msft]

Hi @psoundararajan,

Is solution provided by the community member @techies resolve 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.

[v-kpoloju-msft]

Hi @psoundararajan,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @psoundararajan,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[psoundararajan]

Hey, no. It is not working. I asked a question below, but received no response. The only way I could make it work (for now) is to duplicate the data source and do self-joins. 

[v-kpoloju-msft]

Hi @psoundararajan,
Thank you for the follow-up question.

Attached file gives you the expected results using M query. If your source table is a summarized table (for example, aggregated counts or grouped data), then the script I provided will not work directly because it expects a flat list of item-parent relationships.

If you still have any other questions, please share us the sample input file along with expected output.

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

[v-kpoloju-msft]

Hi @psoundararajan,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @psoundararajan,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @psoundararajan,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[psoundararajan]

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

[raju8004212]

@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 

[v-kpoloju-msft]

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.

[v-kpoloju-msft]

Hi @psoundararajan,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[Shahid12523]

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]

Hello, should this also work if my source table is a summarised table? I'm unable to replace YourTable with the summarized table name.

[wardy912]

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!

[psoundararajan]

Hello @wardy912 , should this also work if my source table is a summarised table? I'm unable to replace YourTable with the summarized table name.