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: ...
Need Help
Tips and Tricks
Tutorial Requests
PVO3's avatar
PVO3
Impactful 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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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):