Forum Discussion

boykin188's avatar
boykin188
Frequent Visitor
7 years ago
Solved

Count # of Entries, In Order, Based on Another Column

Hello,

I have a table that has ID numbers combined with Year and Month - 99.9% of the time, this should be a completely unique entry and should not have any duplicates.

 

So a person with an ID of 200 and a participation date of December 2018 would have an ID: 200201812. A person with an ID of 4356 and multiple participation dates in consecutive months would have an ID of:

4356201810

4356201811

4356201812

4356201901

 

I'm looking to add a count in a column associated with the ID, i.e.:


4356201810         1

4356201811        2

4356201812        3

4356201901        4

4899201803        1

4899201804         2

77201705             1

77201706             2

 

The only requirement is that the first appearence of the ID timewise -- also the lowest numerical value, would be count #1, followed by count#2 if it exists and so on.


Thanks for any help!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    boykin188 -

    You could create 2 calculated columns to separate out the data and a third to get the instance:

     

    User = INT(DIVIDE(Ordering[ID],1000000))
MonthKey = Ordering[ID] - (Ordering[User] * 1000000)
    Instance = COUNTROWS(FILTER(ALL(Ordering), AND(Ordering[User] = EARLIER(Ordering[User]), Ordering[MonthKey] <= EARLIER(Ordering[MonthKey]))))

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    boykin188 -

    You could create 2 calculated columns to separate out the data and a third to get the instance:

     

    User = INT(DIVIDE(Ordering[ID],1000000))
MonthKey = Ordering[ID] - (Ordering[User] * 1000000)
    Instance = COUNTROWS(FILTER(ALL(Ordering), AND(Ordering[User] = EARLIER(Ordering[User]), Ordering[MonthKey] <= EARLIER(Ordering[MonthKey]))))

     

    • boykin188's avatar
      boykin188
      Frequent Visitor
      7 years ago

      Anonymous I think I see what you're trying to do! When I try to do the Instance column though, I'm getting an "EARLIER/EARLIEST refers to an earlier row context which doesn't exist"

      INSTANCE = COUNTROWS(FILTER(ALL(Table_name),
      AND(Table_name[USER] = EARLIER(Table_name[USER]),
      Table_name[MonthKey] <= EARLIER(Table_name[MonthKey]))))
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        boykin188 - Are you creating a Calculated Column or a Measure?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

    This M code works fine

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "ID", "ID - Copy"),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"ID - Copy", type text}}, "en-IN"), "ID - Copy", Splitter.SplitTextByPositions({0, 6}, true), {"ID - Copy.1", "ID - Copy.2"}),
    Partition = Table.Group(#"Split Column by Position", {"ID - Copy.1"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"ID", "Index"}, {"ID", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Partition",{"ID - Copy.1"})
in
    #"Removed Columns"