Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Match data based on other column by date

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 

Need Help
Tips and Tricks
Tutorial Requests

4 Replies

  • PVO3's avatar
    PVO3
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        PVO3
        Icon for Impactful Individual rankImpactful Individual
        4 years ago

        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