New column indicating if row is the first instance of the value for the Entity ID in PowerQuery Editor

user_12115981 0
Viewed 578

I currently have a column that is created using the following DAX formula which indicates if the listed activity is the first one ever for that Entity ID:

First Time Activity = 

if('Activity Table'[Timestamp]=

CALCULATE(min('Activity Table'[Timestamp]),
    filter('Activity Table',
        'Activity Table'[Entity ID] = earlier('Activity Table'[Entity ID]) && 
        'Activity Table'[Activity Name] = earlier('Activity Table'[Activity Name])
            )
    )

,1,BLANK())

Example

But I need this now to be a column made in PowerQuery instead of DAX. Any help on the PowerQuery formula would be much appreciated.

Thanks

m powerbi powerquery
1 Answers

In the query editor, you can do the following steps:

  1. Group by ID and Activity taking the min over the Date column as its aggregation.
  2. Merge that grouped table back onto the original table (matching on ID and Activity and using left outer join).
  3. Expand the min date column.
  4. On the merged and expanded table, replace non-nulls in the expanded column with 1.
user_7652260
Related