Search code examples
databasepowerbipowerquery

Combining Rows in Power BI/Power Query

Let's say I coach a track team, and I keep a log of my students' 800m times over time in PowerBI. My 800m dash table looks something like

Student Date 800m
Rhyne 6/24/24 2:15
Rhyne 6/26/24 2:10
Rhyne 6/28/24 2:12
Alisha 6/24/24 2:09
Alisha 6/26/24 2:05
Alisha 6/28/24 2:03

I would like to have a separate table that is the average for the week:

Student Average Time
Rhyne 2:12
Alisha 2:06

How can I accomplish this in PowerBI? The biggest issue I see is that I am effectively destroying or combining multiple rows into a single one, which is something I have not encountered before.

I have a separate table of general student details:

Student Student ID Class
Rhyne 38356929 Junior
Alisha 96821695 Senior

I was hoping that I could use Power Query to Add Column to this table, and have the value in each row be my students' average time that week. However, I could only access the data in this particular table to Add Column, and not any other table. What else could I do?

Solution

  • You can make the summarization table like this:

    Right click the query you want to summarize and select "Reference". A new query pops up that builds on it.

    Now select the column you want to group by, in your case "Student", right click and select "Group by". Give the summarization column a name, select as operation "average" and as column the times, in your case "800m".

    Then, you can merge it with your existing student table and expand only the average column.