Create a repeating set of IDs

Nick Burns

Nick Burns

Member
Joined
May 24, 2017
Messages
162
Reaction score
0
Points
16
Excel Version(s)
Office 365
I need to create a repeating set of numbers (1 - 10) for a list of dates, however, there is a root date that needs to be set to 1. All dates after that are increased by 1 up to 10, repeating. All dates before that 10 - 1, repeating. :confused2:

The root date will be a parameter that can be changed, and the ending date will keep increasing.

I've attached the workbook file with the table and requested output.

Thanks for any help or guidance!
 

Attachments

  • GroupDates.xlsx
    24.3 KB · Views: 36
Hi Nick,
This is draft only
Code: 
let
    RootDate = DateTime.Date(Excel.CurrentWorkbook(){[Name="Root"]}[Content]{0}[Root Date]),
    Source = Excel.CurrentWorkbook(){[Name="DateList"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Dates", Order.Descending}}),
    Custom1 = Table.FirstN(#"Sorted Rows", each [Dates] >= RootDate),
    Tbl1 = Table.Sort(Table.FromColumns({ List.Reverse(Custom1[Dates]), List.Transform(List.Positions(Custom1[Dates]), each Number.Mod(_, 10)+1)}, {"Dates", "ID"}),{{"Dates", Order.Descending}}),
    Custom2 = Table.LastN(#"Sorted Rows", each [Dates] < RootDate),
    #"Added Index" = Table.AddIndexColumn(Custom2, "ID", 0, 1),
    Tbl2 = Table.TransformColumns(#"Added Index", {{"ID", each 10- Number.Mod(_, 10)}}),
    Custom3 = Table.Combine({Tbl1,Tbl2}),
    #"Changed Type1" = Table.TransformColumnTypes(Custom3,{{"Dates", type date}, {"ID", Int64.Type}})
in
    #"Changed Type1"
 
Thanks Bill - That works like a charm... now to study it and understand it.
 
[Still practising on these old threads]
I learnt about Table.FromRows today:
Code: 
let
    RootDte= Date.From(Excel.CurrentWorkbook(){[Name="Root"]}[Content][Root Date]{0}),
    Source = Excel.CurrentWorkbook(){[Name="DateList"]}[Content],
    DatesList = Table.TransformColumnTypes(Source,{{"Dates", type date}})[Dates],  
    rptcnt = Number.RoundUp(Table.RowCount(Source)/10)+1,
    NumberList =  List.FirstN(List.Skip(List.Reverse(List.Repeat({1..10},rptcnt)),10-Number.Mod(List.PositionOf(DatesList,RootDte)+1,10)),Table.RowCount(Source)),
    Result = Table.FromRows(List.Zip({DatesList,NumberList}),{"Dates","ID"}),
    TheEnd = Table.TransformColumnTypes(Result,{{"Dates", type date}, {"ID", Int64.Type}})
in
    TheEnd
 
Last edited:
Thanks, but you have the numbering backwards (compare the output provided by Bill)

I'm looking through your solution to see where the logic gets turned around. :)

Edit: Found it:
NumberList step becomes:
Code: 
= List.FirstN(List.Skip(List.Repeat({1..10},rptcnt),10-Number.Mod(List.PositionOf(DatesList,RootDte),10)),Table.RowCount(Source))
 
Last edited:
Actually, just sorting the DatesList works just as well!

Thanks again for your solution! :thumb: :rockon:
 
True enough, sorry 'bout that.
 
No apologies necessary -- you're solution uses less steps! :) You mentioned that you just learned Table.FromRows() -- Is there a training course you're following?
 
No training course. Just going through old threads, looking at the documentation, Ken's Data Monkey book, reading articles on the web such as https://bengribaudo.com/blog/2017/1...mer-part1-introduction-simple-expressions-let,
trial and error etc.
Table.FromRows came from looking for a function that would put two lists side by side as a table - I'd been looking for a function with 'List' in it but didn't find it - I found this one ultimately by looking for a function which does the opposite and came across a response in a thread on stackoverflow which mentioned both.
 
Last edited:
Hi, Nick,

I was interested in the question and since I can't quite understand the solution of P45cal at the moment, I tried to develop a solution that is easier to understand.

Since I have now succeeded, here is my solution, where I determine by how much I have to increase the index, related to the position of the rootdate, to get the correct remainder from the division by 10.
 

Attachments

  • xlguru - Create a repeating set of IDs.xlsx
    37.2 KB · Views: 11
You must log in or register to reply here.
Back
Top