Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • tamerj1's avatar
    tamerj1
    4 years ago

    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/mrD8taPTCHSWO8P2

    Please 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
danextian's avatar
danextian
Super User
4 years ago

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?

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Yes! i took to loong i had to quick it.

 

Well i have these other columns 

Index level its a column that i use to give order to a dim table like calendar table. I also have extraction date, and another date column...
its is usefull? 

Is there any way to do this but with out going trugh query editor? i mean its really capacity demanding..