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
tamerj1's avatar
tamerj1
Community Champion
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
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks!

I tried again , i replace the " shipment" for my data original data table 'Fact - IR_IM Raw data Azure' but i received the following sign "

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value" any clue?

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Anonymous 

    Would you please double check that you are creating a calculated table not a measure?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Good news, it woorked fine!

      I just see a small thing that dont make sense.I see 1 reroute also posted in 3 rerouted column. 
      The point is IF, new "actual waypoits" shows up , another column will creat .. even if there are 4 or 5 rerouted.  In case of the example below, there are only 2 reroute , no 3 on the original data.. how can i modify that? 

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Yes of course. here you ahve a screen shoot of it.

      Is there anything i can do to avoid that sign? the table looks great if we could get the rest of the columns 

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Last point would be to do exactly as you showed me, but this time i need only a column to show the lastedes  " waypoit actual". insted of having 1 reroute , 2 reorute . Just another "Waypotin" and nex to this a  column with LASTEST.. i tried different this with out succes..:(

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Hi Anonymous 
        Would you like to have a quick meeting perhaps on zoom or teams?