Work flow for Newbie

ERC2

New Member
Joined
Sep 12, 2011
Messages
30
I am trying to learn powerpivot and wanted to know the best way to set up my data. I get an extract of student test data in a spreadsheet. It has the student name, id, grade, and the test scores for a given test. Each time they take another test, it adds a row of data. At the end of the year, most students have 4 or five rows of data. My goal is to link it to another table that has attendance data and another table with discipline data. Each table has a shared student ID. The problem is each table has multiple rows of data for each student. each day absent, each test administration, each infraction is a new row. I would like to have one row with each column representing the cumulative record. Any suggestions as to the most efficient way to do this?

I am brand new to powerpivot and struggling with Russo and Ferrari's book as my guide.
Thanks,
 

Tianbas

Board Regular
Joined
Apr 29, 2014
Messages
101
Sounds like your data are already in the way PowerPivot likes it -> many rows, less columns

What would really help in your model is a Student table that has one ID column per student. If you cant extract that directly take your above student test data, make a pivot out of it with student id and upload that as a separate table to your power pivot model. Now you can create relationships between your test data, the ID table and all your other tables using the student ID. If you create a (Power-) pivot table from that model use the student ID from the ID table in rows and you should be able to connect all the data from the different tables in one pivot
 

ERC2

New Member
Joined
Sep 12, 2011
Messages
30
Sounds like your data are already in the way PowerPivot likes it -> many rows, less columns

What would really help in your model is a Student table that has one ID column per student. If you cant extract that directly take your above student test data, make a pivot out of it with student id and upload that as a separate table to your power pivot model. Now you can create relationships between your test data, the ID table and all your other tables using the student ID. If you create a (Power-) pivot table from that model use the student ID from the ID table in rows and you should be able to connect all the data from the different tables in one pivot
Thanks I will get to it.
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
Also, while Marco and Alberto's book is fantastic, it is also tough on the beginner. I would get Rob Collie's book (not Alchemy, the first one).
 

Forum statistics

Threads
1,082,573
Messages
5,366,372
Members
400,887
Latest member
tporeda

Some videos you may like

This Week's Hot Topics

Top