# 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...   Reply With Quote

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 ...  Reply With Quote

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?!  Reply With Quote

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.  Reply With Quote

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 !  Reply With Quote

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.  Reply With Quote

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"```  Reply With Quote

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

9. Very clever!   Reply With Quote

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.  Reply With Quote

#### Posting Permissions

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