Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Krexx
Helper I
Helper I

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: 

NumberDescriptionLevel 1 Level 1 Descr.Level 2Level 2 Descr.
9999Energy105Testing10501Testing Backup
1111Bootle106PlasticNULLNULL

 

I would like to create the following output: 

Parent_IDChild_IDDescription
 105Testing
10510501Testing Backput
105019999Energy
 106Plastic
1061111Bottle

 

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

1 ACCEPTED SOLUTION
Geradav
Responsive Resident
Responsive Resident

@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.

View solution in original post

3 REPLIES 3
Geradav
Responsive Resident
Responsive Resident

@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

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
Responsive Resident
Responsive Resident

@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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors