Returning Aggregate and Non aggregate values in a Group By query-CodePudding

My data (sample at the end below) consists of events generated during a game played on a given date. I would like to Group the data by the first 2 columns, and for each DAY, take the MIN and MAX of the TIME column to show who was the player that scored the FIRST and LAST point for that game. The issue is when I group the data, I can't see how to not use an aggregate function on the PLAYER column to just give the player name. The result would be one row summary for each game and look like this:

Location,     Date,    1st Point Time, 1st Point Player, Last Point Time, Last Point Player

---

Indoor Hall   1 Jan 22      10:13      Player 4              11:02        Player 7

I can do this (but its very slow) by using 5 queries:

2 queries - 1 each for the MIN and MAX time of the 'Point Scored' Event column, grouped by LOCATION and DATE

2 more queries, left join the results from the query above back to the main dataset linked on Location, Date and Time and the PLAYER column - which gives the FIRST and LAST player

1 more query to combine the results from the 2 queries in step 2. Is there a way to aggregate the data but also to get the PLAYER name in the aggregation query or does it have to be multiple steps (which takes a LONG time to run).

The raw data sample is below.

Location,     Date,    Time,  Event,           Player
-

Indoor Hall   1 Jan 22 09:43  Shot missing     Player 2
Indoor Hall   1 Jan 22 09:52  Ball out of play Player 5
Indoor Hall   1 Jan 22 10:12  Pass             Player 3
Indoor Hall   1 Jan 22 10:13  Point Scored     Player 4
Indoor Hall   1 Jan 22 10:21  Foul             Player 1
Indoor Hall   1 Jan 22 10:22  Point Scored     Player 3
Indoor Hall   1 Jan 22 10:24  Foul             Player 2
Indoor Hall   1 Jan 22 10:30  Point Scored     Player 7
Indoor Hall   1 Jan 22 10:31  Shot             Player 2
Indoor Hall   1 Jan 22 10:52  Ball out of play Player 1
Indoor Hall   1 Jan 22 11:02  Point Scored     Player 7
Indoor Hall   1 Jan 22 11:10  Shot             Player 3
Outdoor Field 1 Jan 22 ...

CodePudding user response：

Try

 let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{ {"Date", type date}, {"Time", type time}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Event] = "Point Scored")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Time", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Location", "Date"}, {
    {"1st Point Time",each [Time]{0}},
    {"1st Point Player",each [Player]{0}},
    {"Last Point Time", each Table.Last(_)[Time]},
    {"Last Point Planter", each Table.Last(_)[Player]}
    })
in #"Grouped Rows"

CodePudding user response：

Here you go.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZIxC8IwEIX/ypHZIZe2iBkdRJ0KHUuHYKsWYk6advDfmxSRIpQL3pLwQj7eu7u6FifXEg1wNNaKjQCEs3GgVLjLnc6zqFV3GuHRe9 7G4SKGpTWvLoBlGg2DKSIJ zDG9A0Al3hGf4G7YMoGARKjTOiNN7DopY sgTIHKak3o1QXWjo2l9IzkMURu1Ak11zggkQxTlJiKNyzgk3mwDJJOdkmwDB75b872R1S5L7ilqyfWXToEbJpQnDad4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, Date = _t, Time = _t, Event = _t, Player = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Date", type date}, {"Time", type time}, {"Event", type text}, {"Player", type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Changed Type",{{"Location", Text.Clean, type text}, {"Event", Text.Clean, type text}, {"Player", Text.Clean, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Location", Text.Trim, type text}, {"Event", Text.Trim, type text}, {"Player", Text.Trim, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each [Event] = "Point Scored"),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Location", "Date"}, {
        {"1st Point Time", each List.Min([Time]), type nullable time}, 
        {"1st Point Player", (x)=>  Table.SelectRows(#"Filtered Rows",(y)=>  y[Time]=  List.Min(x[Time]))[Player]{0} , type nullable text},
        {"Last Point Time", each List.Max([Time]), type nullable time},
        {"Last Point Player", (x)=>  Table.SelectRows(#"Filtered Rows",(y)=>  y[Time]=  List.Max(x[Time]))[Player]{0} , type nullable text}
})

in
    #"Grouped Rows"