Grouping in PowerPivot

[subdued_chip]

Hi,

I have read a few workarounds for grouping in Powerpivot, but the issue I have seems to be unique.

At work I deal with university student records. Each year universities submit records for each of their students and we use this for analysis. Each year we are sent a massive CSV file containing all of the data for the year, and in the past we have imported it into Access, where we have been able to run queries, export data into Excel and then use a series of lookups.

In recent years, the amount of data has become too large for importing into access (tens of millions of records) and we have moved to an online tool which searches the data, automatically applies the lookups and allows us to download a CSV which we can use in a pivot table. This is fine for many searches we do, which may include a few thousand or hundred thousand records of data.

However, we have recently needed to perform some searches which return 1.4 - 2 million records. This is too large for Excel or Outlook to deal with, so I have tried out PowerPivot, which seems to have no problem at all creating pivot tables with this amount of data. The issue I then have with PowerPivot is that in 2008, a University may have called a course BSc English but in 2009 called it BSc(Hons) English. Previously, we would have used the Group function to combine these two results, but now that grouping has been disabled, I cannot see how this would be done.

I would appreciate any suggestions that help me to get around this, and I apologise for the length of the message.

Many thanks,

Adam

 

[RoryA]

Can you create a lookup table with the course name and the group name you want to use, then link that to the main table?

 

[subdued_chip]

Hmm, I don't think so - there are hundreds of ways that a course title could be presented and it is free text so there are misspellings and mistakes. Also, with so many records it would be impossible to go through them all?

 

[RoryA]

I'm not really sure how manual groupings would have been any easier then, if there are that many entries?

 

[subdued_chip]

When we do a search (for example looking for all courses in English), then if a university has three slightly different course titles in three different years we will group them and rename correctly. It saves manually adding them together each time we want to look at that particular course.

We already use lookups in PowerPivot to add various countires together into regions.

 

[RoryA]

I see - unfortunately I don't think there's another option with PowerPivot.