Forum Discussion

eHorizons's avatar
eHorizons
Frequent Visitor
2 years ago
Solved

Create timepoint for observation based on dates

Hi Everyone!

I have a table like the one below with IDs that correspond to a specific person and dates of observation. I am wondering how to create the Timepoint column in bold below. I want to label the first, second, third, etc observation for each person based on the date field. Any ideas?

IDDateTimepoint
1231/1/20232
5462/3/20231
87612/13/20221
1235/8/20211
8766/1/20232
General Comment
Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    hi, eHorizons 

    let
    Source = table_like_the_one_below,
    gr = Table.Group(
        Source, "ID", 
        {{"points", each Table.AddIndexColumn(Table.Sort(_, "Date"), "Timepoint", 1, 1)}}
    ),
    expand = Table.ExpandTableColumn(gr, "points", {"Date", "Timepoint"})
in
    expand

2 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    hi, eHorizons 

    let
    Source = table_like_the_one_below,
    gr = Table.Group(
        Source, "ID", 
        {{"points", each Table.AddIndexColumn(Table.Sort(_, "Date"), "Timepoint", 1, 1)}}
    ),
    expand = Table.ExpandTableColumn(gr, "points", {"Date", "Timepoint"})
in
    expand
  • DallasBaba's avatar
    DallasBaba
    Skilled Sharer
    2 years ago

    eHorizons You can create the Timepoint column in Power Query.

     

    1. Select the ID and Date columns by holding down the Ctrl key and clicking on each column header.
    2. Click on the Group By button in the Transform tab.
    3. In the Group By dialog box, select ID as the Group By column and click on the Advanced button.
    4. In the Advanced dialog box, enter Timepoint as the New column name and select Count Rows as the Operation. You can also enter a custom name for the Count Rows column if you prefer.
    5. Click on OK to close the Advanced dialog box and then click on OK again to close the Group By dialog box.
    6. The Timepoint column will be added to your table with the first observation for each person labeled as 1, the second observation labeled as 2, and so on.

    If you want to label the observations in reverse order (i.e., the most recent observation labeled as 1), you can sort the table by ID and Date in descending order before performing the Group By operation.

     

    NOTE: You can also archive the same result using a DAX Measure:

     

     

    Timepoint = RANKX(FILTER(ALL('Table'), 'Table'[ID] = EARLIER('Table'[ID]) && 'Table'[Date] <= EARLIER('Table'[Date])), 'Table'[Date], , ASC)

     

     

    Please click Accept as a solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item.
     
    If the content was helpful in other ways, please consider giving it a Thumbs Up.