none
Iterating through list by category and date to generate code with incrementing sequence RRS feed

  • Question

  • Hi,

    we have a large data set in a format similar to that shown below.  We need to generate a code for each record in the format [category][YYYYMMDD]-[xxxx] where xxxx is a sequence in the format 0001, 0002 for a given cateogry on a given date.  

    ID      Category           Date           Code to generate

    1        Fruit                 25/6/2018  Fruit20180625-0001

    2        Fruit                 25/6/2018  Fruit20180625-0002

    3        Vegetable         25/6/2018   Vegetable20180625-0001

    4        Vegetable         26/6/2018   Vegetable20180626-0001

    I need to generate the sequence in the order of the item ID, as shown above, so that the lower ID number always receives the lower sequence number where there are multiple items with the same cateogry on a given day.

    I'm at a bit of a loss where to start.  Does anyone have some advice to kick me off in the right direction?  I thought of a function but I need to take into account other records on the same day to get the sequence number. 

    Thanks very much in advance for any ideas.

    Wednesday, November 21, 2018 11:08 PM

Answers

  • Solution using your sample data in an Excel table as source:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Category", type text}, {"Date", type date}}),
        GroupedRows = Table.Group(ChangedType, {"Category", "Date"}, {{"Table", (i) => Table.AddIndexColumn(i, "Index",1), type table}}),
        AddedCodeColumn = 
                   Table.TransformColumns(
                   GroupedRows, 
                   {"Table", (i) => Table.AddColumn(i, "Code", each [Category] &  Date.ToText([Date], "yyyyMMdd") & "-" & Text.PadStart(Text.From([Index]), 4, "0"))}),
        CombinedTables = Table.Combine(AddedCodeColumn[Table]),
        RemovedIndexColumn = Table.RemoveColumns(CombinedTables,{"Index"})
    in
        RemovedIndexColumn

    If your data is coming from a database table, the "Source" step will change, and the "ChangedType" step will be eliminated. 

    Thursday, November 22, 2018 5:50 AM

All replies

  • Solution using your sample data in an Excel table as source:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Category", type text}, {"Date", type date}}),
        GroupedRows = Table.Group(ChangedType, {"Category", "Date"}, {{"Table", (i) => Table.AddIndexColumn(i, "Index",1), type table}}),
        AddedCodeColumn = 
                   Table.TransformColumns(
                   GroupedRows, 
                   {"Table", (i) => Table.AddColumn(i, "Code", each [Category] &  Date.ToText([Date], "yyyyMMdd") & "-" & Text.PadStart(Text.From([Index]), 4, "0"))}),
        CombinedTables = Table.Combine(AddedCodeColumn[Table]),
        RemovedIndexColumn = Table.RemoveColumns(CombinedTables,{"Index"})
    in
        RemovedIndexColumn

    If your data is coming from a database table, the "Source" step will change, and the "ChangedType" step will be eliminated. 

    Thursday, November 22, 2018 5:50 AM
  • Hi Colin,

    thank you, that's fantastic.  I managed to get some way through it yesterday with some help from an article on Ken Puls' site.  I had no idea what Text.PadStart did!  That's going to be very handy.  My way of addressing that issue involved a lot more steps!

    I did the following, but you've done the whole action in one step.

     #"Grouped Rows" = Table.Group(#"Renamed Columns3", {"Date", "Programme"}, {{"Grouping", each _, type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Grouping], "Index",1,1)),

    It would be great to understand a bit more about what your code does.  Would it be possible for you to explain a bit more about what's going on in the GroupedRows step?

    Best regards

    Anikke


    Thursday, November 22, 2018 10:23 PM