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