Page 2 of 2 FirstFirst 1 2
Results 11 to 19 of 19

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

  1. #11
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    89
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.


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

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

    Code:
    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"

  2. #12
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    60
    Articles
    0
    Excel Version
    Office 365
    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.

    Code:
    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"
    Last edited by cyborgski; 2019-08-08 at 08:04 PM.

  3. #13
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    48
    Articles
    0
    Excel Version
    2016
    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.

  4. #14
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    48
    Articles
    0
    Excel Version
    2016
    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??

  5. #15
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    60
    Articles
    0
    Excel Version
    Office 365
    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
    Attached Files Attached Files

  6. #16
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    48
    Articles
    0
    Excel Version
    2016
    Superb, thanks once again a Mike...i'll give a whirl.

  7. #17
    Neophyte Edyth's Avatar
    Join Date
    Oct 2019
    Location
    usa Texas
    Posts
    1
    Articles
    0
    Excel Version
    Duno
    I'm not really sure which side to look at? before or after? if you still need to look at "to" where . this conversation and where the data in the "to" column came from casino free spins . if all -??? on column "after" the, too, not very understandable. Can be please on much detail

  8. #18
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    48
    Articles
    0
    Excel Version
    2016
    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!

  9. #19
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    48
    Articles
    0
    Excel Version
    2016
    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'.

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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