Search code examples
excelpowerbipowerquerym

Returning Aggregate and Non aggregate values in a Group By query

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 01-Jan-22 09:43:00 Shot missing Player 2
Indoor Hall 01-Jan-22 09:52:00 Ball out of play Player 5
Indoor Hall 01-Jan-22 10:12:00 Pass Player 3
Indoor Hall 01-Jan-22 10:13:00 Point Scored Player 4
Indoor Hall 01-Jan-22 10:21:00 Foul Player 1
Indoor Hall 01-Jan-22 10:22:00 Point Scored Player 3
Indoor Hall 01-Jan-22 10:24:00 Foul Player 2
Indoor Hall 01-Jan-22 10:30:00 Point Scored Player 7
Indoor Hall 01-Jan-22 10:31:00 Shot Player 2
Indoor Hall 01-Jan-22 10:52:00 Ball out of play Player 1
Indoor Hall 01-Jan-22 11:02:00 Point Scored Player 7
Indoor Hall 01-Jan-22 11:10:00 Shot Player 3
Solution

  • 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},type time},
    {"1st Point Player",each [Player]{0}},
    {"Last Point Time", each Table.Last(_)[Time],type time},
    {"Last Point Player", each Table.Last(_)[Player]}
    })
in #"Grouped Rows"

    EDITED

    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},type time},
    {"1st Point Player",each [Player]{0}},
    {"Last Point Time", each Table.Last(_)[Time],type time},
    {"Last Point Player", each Table.Last(_)[Player]},
    {"Matching Player", each Table.Last(_)[Player] = [Player]{0},type logical}
    })
in #"Grouped Rows"