# Thread: Creating a Parent Child 'Path' for a Bill Of Material

1. ## Creating a Parent Child 'Path' for a Bill Of Material

Hi All,

I'm currently trying to figure out a way to replicate a formula I utilize in Excel into Power Query to show the parent path against a child (Part Number) item. It does work well, however when the table reaches a large number of rows 10,000+ it slows down to a point where its just unstable!.
I know that within DAX there is a 'PATH' function, however it requires no duplications within the child (Part Number) and this data will always have duplications, as parts can be used on multiple parents.

I've attached a sample table with the formula that is currently used. I've pasted the formula as values from row 4 onwards.

Example Formula

cell C3 - =IFERROR(LOOKUP(2,1/(\$A\$2:A3=(A3-1)),\$B\$2:B3)," ")
cell D3 - =IFERROR(LOOKUP(2,1/(\$A\$2:A3=(A3-1)),\$C\$2:C3)," ")
and so on...

2. No idea - sorry!

Are we looking at a before or an after here? If after, where is the data coming from? If before, what are the rules (explain in WORDS)?

PowerQuery is essentially a data transformation tool ...

3. This is the initial data (Starting Point) Just the Hierarchy (Level) and Part Number.

And yes I get what Power Query is (and I'm using it everyday to great effect!) and I know it would do this particular task very well...….its just How?!

4. Are you suggesting that I don't use PowerQuery to great effect?

I'll have another look and see if I can understand your logic.

5. I'm sure you're very talented in the Power Query ways (I've lots to learn, and this site (and wonderful people like you!) are a great source!)

I can have a go at scribbling on the image if required, but basically I want a path of all the used on's (parents) for a given part based on the hierarchy within column A....im an Engineer sorry !

6. I can't think of a way in PQ ... Yet!

However, here's an all-in-one formula for you (no need to change it for individual columns):

=IFERROR(LOOKUP(2,1/(\$A\$1:\$A1=\$A2-COLUMNS(\$A\$1:A1)),\$B\$1:\$B1),"")

Paste it into C2 and simply copy across and down. I am fairly sure it replicates what you have done.

7. ENT_CHILD_PATH.xlsx

There is probably a recursive way to do this, but my efforts were generating buffer overflow
This is set up for nine potential levels / columns but you could add a few more rows in A/B fashion to extend for more potential levels

Code:
```let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Sorted Rows" = Table.Sort(#"Added Index",{{"Level", Order.Ascending}, {"Index", Order.Ascending}}),
#"Added Custom1" = Table.AddColumn(#"Sorted Rows", "v1", (z) => Table.Last(Table.SelectRows(#"Sorted Rows",each z [Index]> [Index] and z [Level]= [Level]+1))),
#"Expanded1" = Table.ExpandRecordColumn(#"Added Custom1", "v1", {"Part Number"}, {"Parent-1"}),

#"Added Custom2" = Table.AddColumn(#"Expanded1", "v2", (z) => Table.Last(Table.SelectRows(#"Sorted Rows",each z [Index]> [Index] and z [Level]= [Level]+2))),
#"Expanded2" = Table.ExpandRecordColumn(#"Added Custom2", "v2", {"Part Number"}, {"Parent-2"}),

#"Added Custom3" = Table.AddColumn(#"Expanded2", "v3", (z) => Table.Last(Table.SelectRows(#"Sorted Rows",each z [Index]> [Index] and z [Level]= [Level]+3))),
#"Expanded3" = Table.ExpandRecordColumn(#"Added Custom3", "v3", {"Part Number"}, {"Parent-3"}),

#"Added Custom4" = Table.AddColumn(#"Expanded3", "v4", (z) => Table.Last(Table.SelectRows(#"Sorted Rows",each z [Index]> [Index] and z [Level]= [Level]+4))),
#"Expanded4" = Table.ExpandRecordColumn(#"Added Custom4", "v4", {"Part Number"}, {"Parent-4"}),

#"Added Custom5" = Table.AddColumn(#"Expanded4", "v5", (z) => Table.Last(Table.SelectRows(#"Sorted Rows",each z [Index]> [Index] and z [Level]= [Level]+5))),
#"Expanded5" = Table.ExpandRecordColumn(#"Added Custom5", "v5", {"Part Number"}, {"Parent-5"}),

#"Added Custom6" = Table.AddColumn(#"Expanded5", "v6", (z) => Table.Last(Table.SelectRows(#"Sorted Rows",each z [Index]> [Index] and z [Level]= [Level]+6))),
#"Expanded6" = Table.ExpandRecordColumn(#"Added Custom6", "v6", {"Part Number"}, {"Parent-6"}),

#"Added Custom7" = Table.AddColumn(#"Expanded6", "v7", (z) => Table.Last(Table.SelectRows(#"Sorted Rows",each z [Index]> [Index] and z [Level]= [Level]+7))),
#"Expanded7" = Table.ExpandRecordColumn(#"Added Custom7", "v7", {"Part Number"}, {"Parent-7"}),

#"Added Custom8" = Table.AddColumn(#"Expanded7", "v8", (z) => Table.Last(Table.SelectRows(#"Sorted Rows",each z [Index]> [Index] and z [Level]= [Level]+8))),
#"Expanded8" = Table.ExpandRecordColumn(#"Added Custom8", "v8", {"Part Number"}, {"Parent-8"}),

#"Added Custom9" = Table.AddColumn(#"Expanded8", "v9", (z) => Table.Last(Table.SelectRows(#"Sorted Rows",each z [Index]> [Index] and z [Level]= [Level]+9))),
#"Expanded9" = Table.ExpandRecordColumn(#"Added Custom9", "v9", {"Part Number"}, {"Parent-9"}),

#"Sorted Rows1" = Table.Sort(Expanded9,{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Index"})

in #"Removed Columns"```

8. Also, it is crying out for a Table.Buffer in there somewhere

9. Very clever!

10. Originally Posted by horseyride
Also, it is crying out for a Table.Buffer in there somewhere
Table.Sort and Table.SelectRows?

When I added that, and increased the dataset to 12,000+ rows, I got it down from 135 to 35 secs on my machine. Useful, but still slow.
I must admit I don't like this solution, M is a functional language, and this solution befits a procedural language, but seeing as my functional solution didn't work I guess I have to swallow aesthetics and go with it . it is good to get a BoM in PQ.

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•