Results 1 to 9 of 9

Thread: Getting table-of-contents style page numbers from a list of numbers

  1. #1
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,028
    Articles
    0
    Excel Version
    365

    Getting table-of-contents style page numbers from a list of numbers



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

    Say you have a list:
    {2,2,3,3,4,5,6,7,8,9,10,13,14,16,17,18,23,25,30,31,32}

    to convert to:
    2-10, 13-14, 16-18, 23, 25, 30-32

    Any ideas?

    Here's my very convoluted code for a function (as a query):
    Code:
    let
        Source = {2,2,3,3,4,5,6,7,8,9,10,13,14,16,17,18,23,25,30,31,32},
        #"Converted to Table1" = Table.FromList(Source, Splitter.SplitByNothing(),{"ref page"}, null, ExtraValues.Error),
        #"Removed Duplicates" = Table.Distinct(#"Converted to Table1"),
        #"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"ref page", Int64.Type}}),
        SortedRows = Table.Sort(#"Changed Type",{{"ref page", Order.Ascending}}),
        AllNos = {List.Min(SortedRows[ref page])..List.Max(SortedRows[ref page])},
        #"Converted to Table" = Table.FromList(AllNos, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Merged Queries" = Table.NestedJoin(#"Converted to Table", {"Column1"}, SortedRows, {"ref page"}, "Converted to Table", JoinKind.LeftOuter),
        #"Expanded Converted to Table" = Table.ExpandTableColumn(#"Merged Queries", "Converted to Table", {"ref page"}, {"ref page"}),
        #"Sorted Rows" = Table.Sort(#"Expanded Converted to Table",{{"Column1", Order.Ascending}}),
        #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if [ref page]=null then null else 1),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
        #"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom"}, {{"grp", each _, type table [ref page=nullable number, Custom=nullable number]}},GroupKind.Local),
        #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Custom] = 1)),
        #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "min", each List.Min([grp][ref page])),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "max", each List.Max([grp][ref page])),
        #"Added Custom3" = Text.Combine(Table.AddColumn(#"Added Custom2", "pr", each if [min]=[max] then Text.From([min]) else Text.From([min]) & "-" & Text.From([max]))[pr],", ")// else )
    
    in
        #"Added Custom3"
    Can some PQ ace come up with something slick (or just different)?
    Last edited by p45cal; 2021-11-20 at 03:30 AM.

  2. #2
    Seeker Kolyu's Avatar
    Join Date
    Oct 2018
    Location
    Sofia, Bulgaria
    Posts
    16
    Articles
    0
    Excel Version
    2007, 2010, 2013, 2016
    This is interesting.

    I elaborated on your idea and decreased a bit the number of steps.
    Not quite slick but different

    let
    Source = {2,2,3,3,4,5,6,7,8,9,10,13,14,16,17,18,23,25,30,31,32},
    ListToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    AllNos = Table.FromList({List.Min(Source)..List.Max(Source)}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Merged Queries" = Table.NestedJoin(AllNos, {"Column1"}, ListToTable, {"Column1"}, "AllNos", JoinKind.LeftOuter),
    #"Expanded AllNos" = Table.ExpandTableColumn(#"Merged Queries", "AllNos", {"Column1"}, {"Column1.1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded AllNos",{{"Column1", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each [Column1.1]/[Column1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    Grouped = Table.Group(#"Removed Columns", {"Custom"}, {{"grp", each _, type table [Column1.1=nullable number, Custom=nullable number]}},GroupKind.Local),
    #"Added Custom1" = Table.AddColumn(Grouped, "Custom.1", each if [Custom]=1
    then Number.ToText(List.Min([grp][Column1.1]))& "-" &Number.ToText(List.Max([grp][Column1.1]))
    else null),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Custom"}, {{"Count", each Text.Combine([Custom.1],","), type nullable text}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Custom] = 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Count"})
    in
    #"Removed Other Columns"

  3. #3
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    See here my solution,

    Code:
    let 
        Source           = {2,2,3,3,4,5,6,7,8,9,10,13,14,16,17,18,23,25,30,31,32},
        Convert_to_Table = Table.FromList(Source, Splitter.SplitByNothing(),{"ref page"}, null, ExtraValues.Error),
        Change_Type      = Table.TransformColumnTypes(Convert_to_Table,{{"ref page", Int64.Type}}),
        Index_i0         = Table.AddIndexColumn(Change_Type, "i0", 0, 1),
        Index_i1         = Table.AddIndexColumn(Index_i0, "i1", 1, 1),
        Merge_i1_i0      = Table.NestedJoin(Index_i1, {"i1"}, Index_i1, {"i0"}, "Added Index1", JoinKind.LeftOuter),
        Expand_i1_i0     = Table.ExpandTableColumn(Merge_i1_i0, "Added Index1", {"ref page"}, {"ref page.1"}),
        Fill_Down        = Table.FillDown(Expand_i1_i0,{"ref page.1"}),
        Coll_f           = Table.AddColumn(Fill_Down, "f", each if [i0] = 0 then [ref page] else if [ref page.1] - [ref page] > 1 then [ref page.1] else null),
        Fill_Down_f      = Table.FillDown(Coll_f,{"f"}),
        Group_by_f       = Table.Group(Fill_Down_f, {"f"}, {{"t", each List.Max([ref page.1]), type nullable number}}),
        Result           = Text.Combine(Table.AddColumn(Group_by_f, "pr", each if [f]=[t] then Text.From([f]) else Text.From([f]) & "-" & Text.From([t]))[pr],", ")
    in
        Result

  4. #4
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    Here the query condensed a little more

    Code:
    let 
        Source           = Table.TransformColumnTypes(Table.FromList({2,2,3,3,4,5,6,7,8,9,10,13,14,16,17,18,23,25,30,31,32}, Splitter.SplitByNothing(),{"page"}, null, ExtraValues.Error),{{"page", Int64.Type}}),
        Index_i0         = Table.AddIndexColumn(Source, "i0", 0, 1),
        Index_i1         = Table.AddIndexColumn(Index_i0, "i1", 1, 1),
        Merge_i1_i0      = Table.NestedJoin(Index_i1, {"i1"}, Index_i1, {"i0"}, "Added Index1", JoinKind.LeftOuter),
        Expand_i1_i0     = Table.ExpandTableColumn(Merge_i1_i0, "Added Index1", {"page"}, {"page.1"}),
        Fill_Down        = Table.FillDown(Expand_i1_i0,{"page.1"}),
        Fill_Down_f      = Table.FillDown(Table.AddColumn(Fill_Down, "f", each if [i0] = 0 then [page] else if [page.1] - [page] > 1 then [page.1] else null),{"f"}),
        Group_by_f       = Table.Group(Fill_Down_f, {"f"}, {{"t", each List.Max([page.1]), type nullable number}}),
        Result           = Text.Combine(Table.AddColumn(Group_by_f, "pr", each if [f]=[t] then Text.From([f]) else Text.From([f]) & "-" & Text.From([t]))[pr],", ")
    in
        Result

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,028
    Articles
    0
    Excel Version
    365
    I haven't got a lot of time right now and will respond more fully later. Just a couple of points:
    • I didn't say it, but don't assume the initial list is sorted
    • My aim is to make this a function, so the first step (Source) in this query-to-become-a-function should be just a list on its own

    Anyway, try:{2,2,2,2,4,23,5,5,6,7,8,8,9,10,23,13,14,16,17,18,8,8,8,23,25,30,31,32,35} as another test sequence. The 8s disappear, and preferably it should appear as it would in an appendix/TOC: 2, 4-10, 13-14, 16-18, 23, 25, 30-32, 35
    I hope you don't think I'm moving the goal posts!

  6. #6
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    No, you are right. I have not tested with any other sequences.

  7. #7
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    126
    Articles
    0
    Excel Version
    Excel 365
    Code:
    let
        Source = {2,2,2,2,4,23,5,5,6,7,8,8,9,10,23,13,14,16,17,18,8,8,8,23,25,30,31,32,35},
        ListToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Removed Duplicates" = Table.Distinct(ListToTable),
        #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Column1", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Indeks", 0, 1, Int64.Type),
        #"Inserted Subtraction" = Table.AddColumn(#"Added Index", "Subtraction", each [Column1] - [Indeks], type number),
        #"Grouped Rows" = Table.Group(#"Inserted Subtraction", {"Subtraction"}, {{"Min", each List.Min([Column1]), type number}, {"Max", each List.Max([Column1]), type number}, {"Count", each Table.RowCount(_), Int64.Type}}),
        #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Min", type text}, {"Max", type text}}),
        #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Count] = 1 then [Min] else [Min] & "-" & [Max]),
        Custom1 = Text.Combine(#"Added Conditional Column"[Custom], ", ")
    in
        Custom1

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,028
    Articles
    0
    Excel Version
    365
    Thanks to all, the various approaches have been an education.

    Kolyu
    Took me a little while to work out the algorithm, and as you say a different approach. I liked the each [Column1.1]/[Column1]) which saved on the more convoluted if..then..else of mine. Worked perfectly on the first sequence, but gave 2-10,13-14,16-18,23-23,25-25,30-32 on the second (msg#5) where it didn't reduce the likes of 25-25 to just 25

    pinarello
    The 2nd code is considerably shorter than mine and worked well on the first test. It didn't fare so well with the 2nd test sequence (msg#5) (resulting in 2, 4, 23, 16-18, 25, 30-32, 35 instead of 2, 4-10, 13-14, 16-18, 23, 25, 30-32, 35); I tried fixing it by adding a sort as the 2nd step, but then discovered the Fill_Down step messed up the sorting (!?), so had to sort again after that step on the i0 column, then it gave a perfect answer and is still shorter than mine.

    Bill Szysz
    Then Bill pops in with a diamond where the Inserted Subtraction step is the star attraction.
    Would it be inefficient to miss out the Count aggregation in the Grouped Rows step then later instead of:
    each if [Count] = 1 then [Min] else [Min] & "-" & [Max]
    have:
    each Text.Combine(List.Distinct({[Min],[Max]}),"-")

  9. #9
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    Yes, you have to come up with Bill's line of thinking first

Posting Permissions

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