Forum Discussion
Create parent child table based on multiple levels
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
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.
3 Replies
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 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.
