Creating a Parent Child 'Path' for a Bill Of Material

[jilbobagins]

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





Please Help!!

 

[AliGW]

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

 

[jilbobagins]

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?!

 

[AliGW]

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.

 

[jilbobagins]

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 !

 

[AliGW]

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.

 

[horseyride]

View attachment 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

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"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"

 

[horseyride]

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

 

[AliGW]

Very clever!

 

[Bob Phillips]

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 :Cry: :violin:. it is good to get a BoM in PQ.

 

[horseyride]

This View attachment ENT_CHILD_PATH.2.xlsx works as well. No idea which is more efficient

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"Level", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"Level", type text}}, "en-US")[Level]), "Level", "Part Number", List.Sum),
    Lookup = Table.FillDown(#"Pivoted Column",{"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom1", each Table.Column(Lookup, Text.From([Level]-1)){[Index]}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom2", each Table.Column(Lookup, Text.From([Level]-2)){[Index]}),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom3", each Table.Column(Lookup, Text.From([Level]-3)){[Index]}),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom4", each Table.Column(Lookup, Text.From([Level]-4)){[Index]}),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom5", each Table.Column(Lookup, Text.From([Level]-5)){[Index]}),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Custom6", each Table.Column(Lookup, Text.From([Level]-6)){[Index]}),
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "Custom7", each Table.Column(Lookup, Text.From([Level]-7)){[Index]}),
    #"Added Custom8" = Table.AddColumn(#"Added Custom7", "Custom8", each Table.Column(Lookup, Text.From([Level]-8)){[Index]}),
    #"Added Custom9" = Table.AddColumn(#"Added Custom8", "Custom9", each Table.Column(Lookup, Text.From([Level]-9)){[Index]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom9",{"Index"})
in #"Removed Columns"

 

[cyborgski]

Building on horseyride's original idea, I somehow ended up here. I suspect that it might be a little faster because I was able to get in a table.buffer.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    Buffer = Table.Buffer(#"Added Index"),
    AddParents = Table.AddColumn(Buffer, "Parents", (row) =>  Table.SelectRows(Buffer, each [Level]< row[Level]  and [Index] < row[Index] )),
    Transform = Table.TransformColumns(AddParents,{{"Parents",each Table.Group(_, {"Level"},  {{"Part Number", each List.Last([Part Number]), type number}}     ) , type table}}),
    #"Expanded Parents" = Table.ExpandTableColumn(Transform, "Parents", {"Level", "Part Number"}, {"Parents.Level", "Parents.Part Number"}),
    AddPriorLevel = Table.AddColumn(#"Expanded Parents", "PriorLevel", each  if [Parents.Part Number] = null then "" else Text.From([Level]-[Parents.Level]), type text),
    #"Removed Columns" = Table.RemoveColumns(AddPriorLevel,{"Parents.Level"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[PriorLevel]), "PriorLevel", "Parents.Part Number", List.Sum),
    #"Sorted Rows1" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"Index", ""})
in
    #"Removed Columns1"

 

[jilbobagins]

Thanks all for taking a look at this !!! I’m going to have a play tomorrow
I’m going to use this to compare to Bills of material together once the path is combined together.

 

[jilbobagins]

Hi All , I am utilsing this function however it is painfully slow when size of the docs increase into the 1000's rows. Any thoughts on how the speed of this could be improved at all??

 

[cyborgski]

Hi jilbobagins,

The time required on this approach grows exponentially with the number of records. It will not go faster. I rewrote with an algorithm that grows linearly. The approach is as follows:

1) Determine the maximum number of levels in the data.
2) Break the part out to a column number based on the level.
3) Fill down the column
4) Adjust the columns based on the levels.
5) Remove unneeded columns.

You should find this approach works a lot more quickly.

Regards,
Mike

 

[jilbobagins]

Superb, thanks once again a Mike...i'll give a whirl.

 

[jilbobagins]

Mike,
I'm using you 'Levels','BreakOutLevel' and 'Filled Down' and then unpivoting the data for another function.

I would like the fill down function though only to fill down if the 'level' is not less than the current row. Hoping this makes sense to you. I can include a snapshot if required.

Thanks in advance!

 

[jilbobagins]

Just thinking about this, basically what I need is 'Highest Level' in the 'Levels' list to be deleted and then not shown in the 'BreakOutLevel'.