Aggregate in order to lookup?

[cmcreynolds]

So, I have data from two sources and I want to compare them with calculated fields (or perhaps another way?). However, one is aggregated (by school and program) and the other is not (individual student information).

NOTE: I'm using Excel 2016 and some PQ and PowerPivot aspects are changed slightly, I think?

My original idea was to create a flattened powerpivot table of my student data then add that table back into the data model via PowerQuery.

I'm wondering if there's a way to aggregate my data within my first PowerQuery grab - before it gets to the data model. Another catch is program names are long ("Master of Education in Clinical Elementary School Certification"...or of the sort) in the data and I created a lookup table in PowerPivot that shortened them into codes ("MED-CESC"). If I'm able to aggregate in PowerQuery, can I use lookups to get those codes, too?

I'm sorry that I'm not familiar with M to answer this. Any help is appreciated.

 

[Matt Allington]

Yes you can aggregate in Power Query. Use the Group By button on the home tab

Ken Puls blogged about this just this week. Aggregate data while expanding columns using Power QueryThe Ken Puls (Excelguru) Blog


An alternate approach is to create 2 lookup tables

Lookup Tables
Table 1: just a list of the aggregated values as the primary key
Table 2: a list of the student ids as a primary key with the related aggregation mapping to Table 1 for each student.

Data Tables
Table 3: The student data table
Table 4: The aggregate data table.


Then you do the following
1. join table 4 to table 1
2. join table 3 to table 2
3. join table 2 to table 1
4. Use the column in table 1 in your pivot table rows. This will then filter both of your data tables. You can write aggregate measures against both of your data tables.

 

[cmcreynolds]

Okay, an extension of this same question:

Can you "aggregate" (used loosely here) in PQ without creating a lookup table?

My thought was something like:

=CALCULATE(COUNTROWS(UnAGGTable),[AGGKey]=UnAGGTable[UnAGGKey])

I saw "CALCULATETABLE" and thought that might help, but I wasn't sure how that worked or if it'd be helpful

 

[cmcreynolds]

Oops, in PowerPivot, not in PQ - that was the main difference and I screwed it up.

Can you do the last post in PP?

 

[Matt Allington]

So you have 2 data tables, one is aggregated at some level an the other is not, right?
I will assume a data table with student data (Data1) and a data table with aggregated school data (Data2)

you need a lookup table that has each student (with some ID column) and also which school, program plus a unique key identifying the unique school-program combination. This can simply be a concatenation of school&program) - [Lookup1]
you need a second lookup table with just each unique school,program, and the unique key again Lookup2

Join Data1 to Lookup1 on the student key
Join Data2 to Lookup2 on the school&program key
Join Lookup1 to Lookup2 on the school&program key

Just Lookup2 on rows in your pivot
Create a measure for the SUM(Data1[values]) and SUM(Data2[Values]) columns and place both in your pivot.

Hope that makes sense