Group different rows into one (same data)

Hi Anonymous ,

 

Enter this  M code in a blank query. This might be what you need.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZBNC4JAEIb/y56bmA8316OlFBUdwiAKkdUyAg8lCvXv2z4gqOgUwbzDDDwDD7Neq8juq3N8ampbNBkjM6ABpOxg64a6rh/bbaM6igRJB0wGyW26tIXVucC21ALi+SXktmSwm8I3RLpXSO4wQRi3FTC72dVktgghio3L9JrEwCAajiBOViEsh/3QMUgQtrv7iRewFpV2Plr6IHiz5F9aPhSn8+Rd7QUlxJ429MXvD198kxR6whyw7xmVphc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, ShipmentId = _t, HUUID = _t, shipdate = _t, Waypoints = _t, Waypoints_actual = _t, Extraction_date = _t, __index_level_0__ = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Waypoints", "Waypoints_actual"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Source.Name", type text}, {"ShipmentId", Int64.Type}, {"HUUID", type text}, {"shipdate", type date}, {"Waypoints", type text}, {"Waypoints_actual", type text}, {"Extraction_date", type date}, {"__index_level_0__", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Waypoints2", each if [Waypoints_actual] <> null then [Waypoints_actual] else [Waypoints], type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Waypoints", "Waypoints_actual", "__index_level_0__", "Source.Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Waypoints2", "Waypoints_actual"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Extraction_date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ShipmentId", "HUUID"}, {{"Grouped", each _, type table [ShipmentId=nullable number, HUUID=nullable text, shipdate=nullable date, Extraction_date=nullable date, Waypoints_actual=text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Grouped2", each let x = Table.AddIndexColumn([Grouped], "Index", 0, 1, Int64.Type),

AddCol = Table.AddColumn(x, "Route", each if [Index] = 0 then "Waypoints Actual"else "Reroute " & Text.From([Index] + 1) )
in  AddCol,  type table),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Grouped", "Index"}),
    #"Expanded Grouped2" = Table.ExpandTableColumn(#"Removed Columns1", "Grouped2", {"shipdate", "Waypoints_actual", "Route"}, {"shipdate", "Waypoints_actual", "Route"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Grouped2",{{"shipdate", type date}, {"Waypoints_actual", type text},{"Route", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Route]), "Route", "Waypoints_actual")
in
    #"Pivoted Column"

 

Hi Anonymous 
If you are interested in a DAX solution then I have two options for you:
Option 1: Dynamic number of columns using a measure. This is the solution that I prefer. It is simple and completely dynamic.

You need to have a filter table that contains the Reroute numbers up to the maximum possible. You can just create it in excel and import it. Here is a simple DAX for up to 10 nos

Reroute Order = 
SELECTCOLUMNS ( 
    { 
        ( "1st Reroute", 1 ), 
        ( "2nd Reroute", 2 ), 
        ( "3rd Reroute", 3 ), 
        ( "4th Reroute", 4 ), 
        ( "5th Reroute", 5 ), 
        ( "6th Reroute", 6 ), 
        ( "7th Reroute", 7 ), 
        ( "8th Reroute", 8 ), 
        ( "9th Reroute", 9 ), 
        ( "10th Reroute", 10 ) 
    },
    "Reroute Order", [Value1], "# Reroute Order", [Value2] 
)

This will be just a disconnected table that will be used for slicing the matrix (will be placed on the columns of the matrix)

Then you can create your measure as follows. Place ShipmentId and HUUID in the Rows of the matrix and place the measure in the values. Then place the Reroute Order column from the new table in the columns.

Actual Waypoint = 
VAR T1 = FILTER ( Shipments, [Waypoints_Actual] <> BLANK ( ) )
VAR T2 = ADDCOLUMNS ( T1, "@Rank", RANKX ( T1, [Extraction_date] ) )
VAR CurrentRank = SELECTEDVALUE ( 'Reroute Order'[# Reroute Order] )
VAR T3 = FILTER ( T2, [@Rank] = CurrentRank )
RETURN
    MAXX ( T3, [Waypoints_actual] )

Option 2: Create a new calculated table as follows

Shipments Summary = 
VAR T1 = SUMMARIZE ( Shipments, Shipments[ShipmentId], Shipments[HUUID] )
VAR T2 = 
    GENERATE ( 
        T1, 
        VAR T3 = CALCULATETABLE ( Shipments )
        VAR T4 = 
            ADDCOLUMNS ( 
                T3, 
                "@Length1", LEN ( [Waypoints] ), 
                "@Lenth2", LEN ( [Waypoints_Actual] ), 
                "@Rank", RANKX ( FILTER ( T3, [Waypoints_Actual] <> BLANK ( ) ), [Extraction_date] ) 
            )
        VAR LongestWaypoint1 = MAXX ( TOPN ( 1, T4, [@Length1] ), [Waypoint] )
        VAR LongestWaypoint2 = MAXX ( TOPN ( 1, T4, [@Length1] ), [Waypoint_Actual] )
        VAR FirstReroute = MAXX ( FILTER ( T4, [@Rank] = 1 ), [Waypoints_actual] )
        VAR SecondReroute = MAXX ( FILTER ( T4, [@Rank] = 2 ), [Waypoints_actual] )
        RETURN  
            ROW ( "Waypoint", LongestWaypoint1, "Waypoint_Actual", LongestWaypoint2, "1st Route", FirstReroute, "2nd Route", SecondReroute )
    )
RETURN
    T2

 This solution is not dynamic you you need to hard-code the number of columns and the names of those columns.

Try use "remove duplicates" in Power Query. 

 

https://docs.microsoft.com/en-us/power-query/working-with-duplicates