Finding a root id using multiple nested joins - Is there a more performant option?

[Herbie]

Hello,

I have a table that includes a column containing previous_ids. I want to use this column to find each record's root id. Some records don't have a previous_id and some previous_id's lead to a record that has a previous_id of its own. The longest chain is 5.

This is the desired outcome is this:

id	previous_id	root_id
1	null	1
2	null	2
3	1	1
4	3	1
5	2	2
6	4	1

I have solved this using a sequence of nested joins. Bascially:

= Table.NestedJoin(#"previous_step", {"previous_id_number_1"}, #"foo_step", {"id"}, "previous_id_number_2", JoinKind.LeftOuter)

= Table.NestedJoin(#"previous_step", {"previous_id_number_2"}, #"foo_step", {"id"}, "previous_id_number_3", JoinKind.LeftOuter)

...

This method works, but seems slow and fragile.

Is there a better way to do this in Power Query?

Thanks,

Herbie

[wdx223_Daniel · Accepted solution]

the category of PATH function in DAX can resolve this kind of problems efficiently.

in M language, it may take more resources and time.