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

p45cal

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,403
Reaction score
48
Points
48
Excel Version(s)
365
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:
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"
 
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
 
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
 
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!:)
 
No, you are right. I have not tested with any other sequences.
 
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
 
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]}),"-")
 
Yes, you have to come up with Bill's line of thinking first
 
You must log in or register to reply here.
Back
Top