Page 1 of 2 1 2 LastLast
Results 1 to 10 of 18

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

  1. #1
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    49
    Articles
    0
    Excel Version
    2016

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

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



    Click image for larger version. 

Name:	Capture2.jpg 
Views:	212 
Size:	106.6 KB 
ID:	9314

    Please Help!!
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture.jpg 
Views:	4 
Size:	109.3 KB 
ID:	9313  
    Attached Files Attached Files

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,273
    Articles
    0
    Excel Version
    Office 365 Subscription
    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 ...
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    49
    Articles
    0
    Excel Version
    2016
    Click image for larger version. 

Name:	Capture3.JPG 
Views:	104 
Size:	75.6 KB 
ID:	9315

    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. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,273
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    49
    Articles
    0
    Excel Version
    2016
    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. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,273
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    92
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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],
        #"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"

  8. #8
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    92
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    Also, it is crying out for a Table.Buffer in there somewhere

  9. #9
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,273
    Articles
    0
    Excel Version
    Office 365 Subscription
    Very clever!
    Ali
    Enthusiastic self-taught user of MS Excel!

  10. #10
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,672
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by horseyride View Post
    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 LastLast

Posting Permissions

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