Forum Discussion
Group different rows into one (same data)
Hi all. I need to be able to group different rows, which correspond to the same data, into one. In the image you can see that SHIPMENTID and HUUID is the same for the 3 columns, the only differen...
Hi Anonymous
I though I've had replied back to you with updated solution but for some reason I caanot see my reply. Heree is the sample file https://www.dropbox.com/t/mrD8taPTCHSWO8P2Please try this solution if it works. However, this is not a perfect situation for a 17M rows data.
Shipments Summary 2 = VAR T1 = SUMMARIZE ( Shipments, Shipments[ShipmentId], Shipments[HUUID], "Waypoint", MAX ( Shipments[Waypoints] ) ) VAR T2 = GENERATE ( T1, VAR T3 = CALCULATETABLE ( Shipments ) VAR T4 = ADDCOLUMNS ( T3, "@Rank", RANKX ( FILTER ( T3, [Waypoints_Actual] <> BLANK ( ) ), [Extraction_date] ) ) VAR FirstReroute = MAXX ( FILTER ( T4, [@Rank] = 2 ), [Waypoints_actual] ) VAR SecondReroute = MAXX ( FILTER ( T4, [@Rank] = 1 ), [Waypoints_actual] ) RETURN ROW ( "1st Route", FirstReroute, "2nd Route", SecondReroute,"3rd Route", FirstReroute ) ) RETURN T2
I can imagine how slow my proposed solutions would be withi 17M rows of data. Grouping and th then creating an index column in each uid is essentially looping through each row of uid. Do you have other column that indicates the order of each extract date similar to below?
Sorry I dont have any other column i have just checked and that column does not ahve a unique value for every row.. 😞 Is there any way to solve it ?