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
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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Yes of course! Teams or  zoom for me is fine! please let me know your email and when it works for you? dont know your time zone...