Managing Parent Child data relationships help request

[RappJ]

Good day all,

Attached is a set of sample data that included a raw data set and a desired data set. Any guidance on how to begin moving forward with this would be greatly appreciated. 

• Child knows parent
• Parent does NOT know child
• Need to group all related generatiosn all the way to the parent level
• Smaple dataset goes to grandchildren
• Actual live data can be up to 12 generations deep so I am trying to figure out a way to name all the headers dynamically and null the values for any generations missing data. 

I thought about possibly grouping by the [used in material number] field then expanding the nested tables somehow, but I am currently lost.

Raw Data

Desc	Material Number	Serial Number	Equipment Number	Used in Equipment Number	Current Points	Point Limit
Parent	999999	1	PARENT-9991	null	null	null
Child-1	888888	2	CHILD-8881	PARENT-9991	15000	25000
Grandchild-1A	777777	3	GRANDCHILD-7771	CHILD-8881	2500	6000
Grandchild-1B	666666	4	GRANDCHILD-6661	CHILD-8881	4500	6000
Grandchild-1C	555555	5	GRANDCHILD-5551	CHILD-8881	5000	6000
Child-2	444444	6	CHILD-4441	PARENT-9991	10000	15000
Grandchild-2A	333333	7	GRANDCHILD-3331	CHILD-4441	1500	2250
Grandchild-2B	222222	8	GRANDCHILD-2221	CHILD-4441	2000	2250
Grandchild-2C	111111	9	GRANDCHILD-1111	CHILD-4441	2500	2250
Parent	999999A	1A	PARENT-9991A	null
Child-1	888888A	2A	CHILD-8881A	PARENT-9991A	15000	25000
Grandchild-1A	777777A	3A	GRANDCHILD-7771A	CHILD-8881A	2500	6000
Grandchild-1B	666666A	4A	GRANDCHILD-6661A	CHILD-8881A	2500	6000
Grandchild-1C	555555A	5A	GRANDCHILD-5551A	CHILD-8881A	5000	6000
Child-2	444444A	6A	CHILD-4441A	PARENT-9991A	10000	15000
Grandchild-2A	333333A	7A	GRANDCHILD-3331A	CHILD-4441A	1500	2250
Grandchild-2B	222222A	8A	GRANDCHILD-2221A	CHILD-4441A	2000	2250
Grandchild-2C	111111A	9A	GRANDCHILD-1111A	CHILD-4441A	2500	2250

Desired Formatted Data

Desc

Material Number

Serial Number

Equipment Number

Used in Equipment Number

Current Points

Point Limit

Child-1 Equipment Number

Child-1 Desc

Child-1 Material Number

Child-1 Serial Number

Child-1 -Current Points

Child-1 Max Points

Child-2 Equipment Number

Child-2 Desc

Child-2 Material Number

Child-2 Serial Number

Child-2 -Current Points

Child-2 Max Points

GrandChild-1A Desc

GrandChild-1A Material Number

GrandChild-1A Serial Number

GrandChild-1A Equipment Number

GrandChild-1ACurrent Points

GrandChild-1A Point Limit

GrandChild-1B Desc

GrandChild-1B Material Number

GrandChild-1B Serial Number

GrandChild-1B Equipment Number

GrandChild-1BCurrent Points

GrandChild-1B Point Limit

GrandChild-1c Desc

GrandChild-1c Material Number

GrandChild-1c Serial Number

GrandChild-1c Equipment Number

GrandChild-1cCurrent Points

GrandChild-1c Point Limit

GrandChild-2A Desc

GrandChild-2A Material Number

GrandChild-2A Serial Number

GrandChild-2A Equipment Number

GrandChild-2ACurrent Points

GrandChild-2A Point Limit

GrandChild-2B Desc

GrandChild-2B Material Number

GrandChild-2B Serial Number

GrandChild-2B Equipment Number

GrandChild-2BCurrent Points

GrandChild-2B Point Limit

GrandChild-2C Desc

GrandChild-2C Material Number

GrandChild-2C Serial Number

GrandChild-2C Equipment Number

GrandChild-2CCurrent Points

GrandChild-2C Point Limit

Parent

999999

1

PARENT-9991

null

CHILD-8881

Child-1

888888

2

15000

25000

CHILD-4441

Child-2

444444

6

10000

15000

Grandchild-1A

777777

3

GRANDCHILD-7771

2500

6000

Grandchild-1B

666666

4

GRANDCHILD-6661

4500

6000

Grandchild-1C

555555

5

GRANDCHILD-5551

5000

6000

Grandchild-2A

333333

7

GRANDCHILD-3331

1500

2250

Grandchild-2B

222222

8

GRANDCHILD-2221

2000

2250

Grandchild-2C

111111

9

GRANDCHILD-1111

2500

2250

Parent

999999A

1A

PARENT-9991A

null

CHILD-8881A

Child-1

888888A

2A

15000

25000

CHILD-4441A

Child-2

444444A

6A

10000

15000

Grandchild-1A

777777A

3A

GRANDCHILD-7771A

2500

6000

Grandchild-1B

666666A

4A

GRANDCHILD-6661A

2500

6000

Grandchild-1C

555555A

5A

GRANDCHILD-5551A

5000

6000

Grandchild-2A

333333A

7A

GRANDCHILD-3331A

1500

2250

Grandchild-2B

222222A

8A

GRANDCHILD-2221A

2000

2250

Grandchild-2C

111111A

9A

GRANDCHILD-1111A

2500

2250

Thank you for any assistance you may be able to provide.

Jason 

[BA_Pete · Accepted solution]

Hi @RappJ ,

Please see this recent post on a similar topic where I provide a couple of links for DAX/M solutions:

https://community.powerbi.com/t5/Power-Query/Iterative-Parts-List-How-to-generate-useful-hierarchies... 

The process you're looking for is 'flattening a hierarchy' and, as per my response there, this is potentially complex/time-consuming process, so you probably won't get a full, made-to-measure solution here I'm afraid.

Pete

[RappJ]

Thank you. The PATH option worked.