Forum Discussion
Match data based on other column by date
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.
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 :
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
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):