Create parent child table based on multiple levels

[Krexx]

Hello together, i would like to create a parent child table based on mutiple levels. 

For example i have the following table: 

Number	Description	Level 1	Level 1 Descr.	Level 2	Level 2 Descr.
9999	Energy	105	Testing	10501	Testing Backup
1111	Bootle	106	Plastic	NULL	NULL

I would like to create the following output: 

Parent_ID	Child_ID	Description
	105	Testing
105	10501	Testing Backput
10501	9999	Energy
	106	Plastic
106	1111	Bottle

I was trying to use the function PATH, but i can only use two columns to create a path. For that i need this kinda table. Has anyone an idea?

Thanks in advance

[Geradav]

@Krexx 

If I understood you correctly you want to create a table that looks like your second table based on the first.

I'd used M language with Power Query.

Create 3 queries selecting the columns that you need

• Query 1 => Level 1, Level 1 Descr.
• Query 2 => Level 1, Level 2, Level 2 Descr.
• Query 3 => Level 2, Number, Description

Add a column to query 1 so they all have the same number of colunns.

Rename columns accordingly (Parent_ID, Child_ID, Description) so they all have the same named columns

Append all queries into one query to obtain the desired output.

Let us know if I understood you correctly and if that works for you.

David

[Krexx]

Hello @Geradav, thanks for your response. 
The problem is, that sometimes a number has only one level and sometimes it has 2 or 3 levels. 

[Geradav · Accepted solution]

@Krexx Then I guess you could add up a fourth query filtered on Null in Level 2

• Query 4 => Level 1, Number, Description (where Level 2 = Null)

And update Query 3 to only keep Level 2 where it is not Null

• Query 3 => Level 2, Number, Description (Where Level 2 <> Null)

Otherwise you could share a sample file with all possible senario.