Results 1 to 10 of 10

Thread: Create a repeating set of IDs

  1. #1
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    99
    Articles
    0
    Excel Version
    Office 365

    Create a repeating set of IDs



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

    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.

    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!
    Attached Files Attached Files
    Oh... by the way, YOU'RE WELCOME!

  2. #2
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    112
    Articles
    0
    Excel Version
    Excel 365
    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"

  3. #3
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    99
    Articles
    0
    Excel Version
    Office 365
    Thanks Bill - That works like a charm... now to study it and understand it.
    Oh... by the way, YOU'RE WELCOME!

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,751
    Articles
    0
    Excel Version
    365
    [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 by p45cal; 2020-07-02 at 10:45 AM.

  5. #5
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    99
    Articles
    0
    Excel Version
    Office 365
    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 by Nick Burns; 2020-07-07 at 04:04 PM. Reason: Change Step
    Oh... by the way, YOU'RE WELCOME!

  6. #6
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    99
    Articles
    0
    Excel Version
    Office 365
    Actually, just sorting the DatesList works just as well!

    Thanks again for your solution!
    Oh... by the way, YOU'RE WELCOME!

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,751
    Articles
    0
    Excel Version
    365
    True enough, sorry 'bout that.

  8. #8
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    99
    Articles
    0
    Excel Version
    Office 365
    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?
    Oh... by the way, YOU'RE WELCOME!

  9. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,751
    Articles
    0
    Excel Version
    365
    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/11...xpressions-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 by p45cal; 2020-07-07 at 04:50 PM.

  10. #10
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    36
    Articles
    0
    Excel Version
    Office 365
    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.
    Attached Files Attached Files

Posting Permissions

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