Match data based on other column by date

[Anonymous]

Hi all

Im am looking to match diferrent rows in one ( same data) using other column as data parameter to choose the last one. Maybe looking at the example data will be easyer to understand:

Raw data : 

Desired data: 

I need to keep "waypoints" as it is, and next to this i need a new column " LAST REROUTE" wich would be the last "Waypoint actual" based on Extration_date  ( in this case the one for the 3rd of aug).

I was trying something like this but no sucess:

Fact - IR_IM LAST RE_ROUTE =

VAR T1 = SUMMARIZE ( 'Fact - IR_IM RAW DATA', 'Fact - IR_IM RAW DATA'[ShipmentId],'Fact - IR_IM RAW DATA'[Geography],'Fact - IR_IM RAW DATA'[Internaltional/Domestic], 'Fact - IR_IM RAW DATA'[HUUID], "Waypoint", MAX ( 'Fact - IR_IM RAW DATA'[Waypoints]) )

VAR T2 =

    GENERATE (

        T1,

        VAR T3 = CALCULATETABLE ( 'Fact - IR_IM RAW DATA')

        VAR T4 =

            ADDCOLUMNS (

                T3,

                "@Rank", RANKX ( FILTER ( T3, [Waypoints_Actual] <> BLANK ( ) ), [Extraction_date] )

            )

        VAR FirstReroute = MAXX ( FILTER ( T4, [@Rank] = 4 ), [Waypoints_actual] )

RETURN  

            ROW ( "LAST REROUTE", FirstReroute)

    )

RETURN

    T1

@tamerj1 

[PVO3]

Hi JoseBernardo,

Looking at your example, I'm wondering what the exact logic is. It appears like you would like you want to keep the most recent [Extraction_date] for earch [ShipmentId]. Only exception is that when [Waypoints] is blank, use a non blank [Waypoints] from the same [ShipmentId]. This should be easily achieved within Power Query.

[Anonymous]

mm not sure if its that easy, i need to combined the "waypoint" column with the most recent " waypoint actual" based on extraction date, all in one row... not multiples. Take a look at desired result :

[PVO3]

If that is your logic you can fix it like below. Not the most fancy solution, but you can use the PQ interface.

- Create 2 duplicates of your table in PQ

First duplicate:

- Filter out the blank duplicates.

- Merge original with this duplicate on [ShipmentId] 

- Expand [Waypoint] from duplicate

Every [ShipmentId] now has a [Waypoint]

Second duplicate:

- Group by [ShipmentId], aggragate on max [Extraction_Date]

- Merge original table with second duplicate

- Expand [Extraction date] from second duplicate

- Create a custom column original.[Extraction_Date] = secondduplicate[Extraction_Date]

- Filter original table by this custom column = TRUE

Most recent [ShipmentId] is filtered

[Anonymous]

I have over 17 millions rows, i need to avoid going into power query. I did the followin on DAX and resulted correct ( faster aswell):