none
Dynamicly Renumber Heirarchal Numerical Titles RRS feed

  • Question

  • That title seems like a handful so let me try to sum it up, I work with lists of tasks that are usually hierarchal in nature, such as 1.2, 1.2, 1.2.1, 1.2.1.1, 1.2.1.2, 1.2.1.3, etc

    Given the above example, say task 1.2.1.2 is no longer needed. Generally these tasks go up into the teens, 20s, 30s, and erasing one has lead to a lot of manual hand renumbering of these items, or my shortcut is to just take these numbers (which are in their own column, A) and erase from the backend. While that works, I figure there has to be a way to do it programmatically using tools like power query and using the period as a delimiter. I've tried figuring out how to do it using standard excel formulas but always get stuck on the different levels of parentage. However, I am an extreme novice in this field and am just working my way through Collect, Combine, and Transform Data using Power Query in Excel and Power BI 1st Ed and could use some help on this issue if at all possible. Thank you. 

    Monday, April 22, 2019 2:51 PM

Answers

  • Hi Jdeuel,

    Suppose, you have such dataset:

    task description
    3.2.4 R
    3.2.5 S
    1.2 C
    1.2.1 D
    1.2.2 E
    3.2.5.3 T
    1.2.5 F
    1.2.5.2 G
    1.2.5.5 H
    1.2.8 I
    1.2.8.1 J
    1.2.8.3 K
    1.2.8.5 L
    1.4 M
    1.4.2 N
    1.4.2.4 O
    3 P
    1.1 B
    1 A
    3.2.5.5 U
    3.2 Q

    Then, you may use following technique:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        copy = Table.DuplicateColumn(Source, "task", "copy task"),
        cols = {"t1","t2","t3","t4"},
        split = Table.SplitColumn(copy, "copy task", Splitter.SplitTextByDelimiter("."), cols),
        types = Table.TransformColumnTypes(split, List.Transform(cols, each {_, Int64.Type})),
        sort = Table.Sort(types, List.Transform(cols, each {_, 0})),
    
        
        func = (tbl as table, col as text) =>
    let
        group = Table.Group(tbl, col, {"temp", each _}),
        listOld = List.Buffer(Table.Column(group, col)),
        listNew = if List.Contains(listOld, null) then {null}&{1..(List.Count(listOld)-1)} else {1..List.Count(listOld)},
        table = Table.FromColumns(Table.ToColumns(group)&{listNew}, Table.ColumnNames(group)&{"Value"}),
        replace = Table.ReplaceValue(table, each [temp], each Table.FillUp(Table.Combine({[temp], Table.FromValue([Value])}), {"Value"}), Replacer.ReplaceValue, {"temp"}),
        combine = Table.Combine(replace[temp]),
        filter = Table.SelectRows(combine, each ([task] <> null)),
        rename = Table.RenameColumns(filter,{"Value", col&"N"})
    in
        rename,
    
    
        group = Table.Group(func(sort,"t1"), "t1N", {"x", each Table.Group(func(_,"t2"),"t2N",
                                                     {"x", each Table.Group(func(_,"t3"),"t3N",
                                                     {"x", each Table.Group(func(_,"t4"),"t4N",
                                                     {"x", each _})})})}),
        combine = Table.Combine(Table.Combine(Table.Combine(Table.Combine(group[x])[x])[x])[x]),
        taskNew = Table.AddColumn(combine, "taskNew", each Text.From([t1N])&
                                            (if [t2N] = null then "" else "."&Text.From([t2N])) &
                                            (if [t3N] = null then "" else "."&Text.From([t3N])) &
                                            (if [t4N] = null then "" else "."&Text.From([t4N]))),
        remove = Table.SelectColumns(taskNew,{"task", "taskNew", "description"})
    in
        remove
    
    • Edited by Aleksei Zhigulin Thursday, April 25, 2019 1:07 PM
    • Marked as answer by Jdeuel Friday, April 26, 2019 3:16 PM
    Thursday, April 25, 2019 12:30 PM

All replies

  • Excel 365 Power Query.
    Assuming you are using the Dewey Decimal System.
    This query shows how to sort these numbers correctly.
    http://www.mediafire.com/file/sej8waagw2h0339/04_12_16b.xlsx/file
    http://www.mediafire.com/file/a5fyttfoskmzamh/04_12_16b.pdf/file
    Need shared file to address other operations.

    Tuesday, April 23, 2019 11:59 PM
  • Assuming you are using the Dewey Decimal System.

    The numbering has absolutely nothing to do with the Dewey Decimal System.

    Wednesday, April 24, 2019 8:44 PM
  • Hi Jdeuel,

    Suppose, you have such dataset:

    task description
    3.2.4 R
    3.2.5 S
    1.2 C
    1.2.1 D
    1.2.2 E
    3.2.5.3 T
    1.2.5 F
    1.2.5.2 G
    1.2.5.5 H
    1.2.8 I
    1.2.8.1 J
    1.2.8.3 K
    1.2.8.5 L
    1.4 M
    1.4.2 N
    1.4.2.4 O
    3 P
    1.1 B
    1 A
    3.2.5.5 U
    3.2 Q

    Then, you may use following technique:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        copy = Table.DuplicateColumn(Source, "task", "copy task"),
        cols = {"t1","t2","t3","t4"},
        split = Table.SplitColumn(copy, "copy task", Splitter.SplitTextByDelimiter("."), cols),
        types = Table.TransformColumnTypes(split, List.Transform(cols, each {_, Int64.Type})),
        sort = Table.Sort(types, List.Transform(cols, each {_, 0})),
    
        
        func = (tbl as table, col as text) =>
    let
        group = Table.Group(tbl, col, {"temp", each _}),
        listOld = List.Buffer(Table.Column(group, col)),
        listNew = if List.Contains(listOld, null) then {null}&{1..(List.Count(listOld)-1)} else {1..List.Count(listOld)},
        table = Table.FromColumns(Table.ToColumns(group)&{listNew}, Table.ColumnNames(group)&{"Value"}),
        replace = Table.ReplaceValue(table, each [temp], each Table.FillUp(Table.Combine({[temp], Table.FromValue([Value])}), {"Value"}), Replacer.ReplaceValue, {"temp"}),
        combine = Table.Combine(replace[temp]),
        filter = Table.SelectRows(combine, each ([task] <> null)),
        rename = Table.RenameColumns(filter,{"Value", col&"N"})
    in
        rename,
    
    
        group = Table.Group(func(sort,"t1"), "t1N", {"x", each Table.Group(func(_,"t2"),"t2N",
                                                     {"x", each Table.Group(func(_,"t3"),"t3N",
                                                     {"x", each Table.Group(func(_,"t4"),"t4N",
                                                     {"x", each _})})})}),
        combine = Table.Combine(Table.Combine(Table.Combine(Table.Combine(group[x])[x])[x])[x]),
        taskNew = Table.AddColumn(combine, "taskNew", each Text.From([t1N])&
                                            (if [t2N] = null then "" else "."&Text.From([t2N])) &
                                            (if [t3N] = null then "" else "."&Text.From([t3N])) &
                                            (if [t4N] = null then "" else "."&Text.From([t4N]))),
        remove = Table.SelectColumns(taskNew,{"task", "taskNew", "description"})
    in
        remove
    
    • Edited by Aleksei Zhigulin Thursday, April 25, 2019 1:07 PM
    • Marked as answer by Jdeuel Friday, April 26, 2019 3:16 PM
    Thursday, April 25, 2019 12:30 PM
  • Your output looks very much like what I am trying to do, however I am running into difficulty using your provided code. I use your sample values including headers and perform a new query using a table, Table1, as referenced. However when I use that code in the advanced editor it throws Expression.Error: We cannot convert the value 1 to type Text. 

    Value=1

    Type=Type

    Even on a lark I tried to manually format the cells as text but it didn't budge. This error starts in the 'func' Applied Step. Am I doing something silly here to cause this to not work?

    Thursday, April 25, 2019 6:57 PM
  • It works for me. Make sure that your task and description are both type text and don't contain spaces at the start or end. Which changes did you make to the code?

    Friday, April 26, 2019 7:45 AM
  • On my machine it works just fine. Check task column values, when you paste them to Excel. Some values may automatically converted to dates.
    Friday, April 26, 2019 8:03 AM
  • Ok, I got it figured out! Looks like when it created the table in the query editor it was indeed not assigning them the text type, despite the spreadsheet having them as that. After adding in a step and manually converting the column there everything worked fine. Thank you for the help!
    Friday, April 26, 2019 3:20 PM