Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
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):
User | Count |
---|---|
139 | |
70 | |
68 | |
52 | |
52 |
User | Count |
---|---|
209 | |
92 | |
64 | |
60 | |
57 |