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've never tried connecting to a datalake so I am not sure how it is done. If you post the full M-script of connecting to a table in a datalake, I might have an idea.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

When i paste the code on a blanck query somethig seems to break , everything turns red. am i missing something?

 

  • danextian's avatar
    danextian
    Super User
    4 years ago

    Go to Advanced Editor, clear everything and paste the code. You can right-click the query  or select the query, go to Home tab and then Advanced Editor.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks! now seems to be working. But only aplly for 1 row.. how do i do to get all my data trough these ?  there is only one row after applying 

       

       

      • danextian's avatar
        danextian
        Super User
        4 years ago

        There is only one row because the data used was just your sample data, not the actual data. The Source step must be changed so it points to the actual data.