powerbipowerquerypowerbi-desktopdata-cleaningm

Identifying the max in a group using PowerQuery


I have a table:

trip_id stop_sequence
Y1 1
Y1 2
Y1 3
Y1 4
Y1 5
Y2 1
Y2 2
Y2 3
Y2 4

I want to add a custom column that has a value of TRUE for the record which has the highest value of stop_sequence within a group that is grouped by trip_id. In my case, the 5th and 9th row will have TRUE.

What should be the expression of the custom column?


Solution

  • let a = Table.SelectRows(#"Changed Type", (x)=> x[trip_id] = [trip_id]),
    b = List.Max(a[stop_sequence])
    in if b = [stop_sequence] then true else null
    

    enter image description here

    enter image description here