Parent/ Child Hierarchy from table

[Johno58jj]

Hi,
This one has me baffled.
I have 2 power queries. The first one is parent and child data and is called SAP Orgs. The SAP Orgs column is made up of sets of numbers which are either parent or child data.
I want to compare it to the Org Checker query and create a new column called Parent Orgs which only contains the parent orgs.
The Org_Checker query shows several columns Level 1 - Id, Level 2 Id and so on to Level 12 - ID.
Level 1 - Id is the parent of Level 2 - ID and Level 2 - Id is the parent of Level 3 Id and so on and so forth.
How would you set up the query in Power Query to do this?

 

[p45cal]

This one has me baffled.

Me too.
Can you confirm which table you want to add a column to?
Looking at your data, there's not a single matching 8-character string between the tables, so I haven't the foggiest what I'm looking for.

 

[Johno58jj]

Hi p45Cal,
Sorry for the confusion.
I've added an updated SAP Orgs file to show what the final result would look like.
In line 4, SAP Orgs shows 3 sets of numbers but there's only one parent 72002828.
The same is true for line 16, only 2 parent numbers 70071360-72008775.

As for the Org Checker - I've included the full file and it should make more sense hopefully.

 

[p45cal]

What makes an 8-digit reference a parent?
Is it that if it's in the Org_Checker table and has another entry immediately to its right?
Or is it that it isn't in the Level 12 (column L)?

If it's the former, this is what I get:

which is considerably different from your expected results, so what does make an 8-digit reference a parent?

 

[Johno58jj]

If you look at SAP Orgs, line 4 that has this data in it 72002828-72014175-72014176
if you search 72002828 in the Org checker, you see it sits on
line 1935 in Level 8 - ID
Line 1936 in level 8 - ID
Line 1936 in level 8 - ID
Line 1937 in Level 8 - ID

72014175 sits on line 1937 but in Level 9 - ID
72014176 sits on line 1938 but in Level 9 - ID.

Whatever sits in level 8 is the parent of anything in Level 9 so in this instance 72002828 is the parent of the 3 numbers.

So with the Sap Orgs sheet, if there are multiple numbers in the cell (like line 4) they need to be compared to the Org Checker and any child numbers removed.

You might find one number is in level 3 for example, and the others are in say, level 6, level 7 and level 10 therefore the level 3 number is the parent.

Another example, if you find 3 numbers in level 5 and the rest are in level 6 and say, level 8 - the 3 in level 5 are the parents of anything in levels 4 to 12.

 

[Johno58jj]

Actually let me get back to you - I understand what you're saying and now I need to confirm the data in the org checker... something is not right here....
Again, sorry for all the confusion