Normal Queries in Powerpivot with MAX

[mike2015]

Hello

I'm not an expert on queries, experienced in Excel but totally new in excel pivot tables. I have a DB2 database with hundred millions of rows. I want to perform some excel calculations in it, so i have to preselect the data. This takes a lot of time. Now i see that Powerpivot seems to have the database stored in memory and so i thought i could do this in powerpivot and save time and especially improve the calculations in excel. I have tried PowerQuery but can't connect to database. I have made a query in DB2 and have imported a fraction of the database to powerpivot, but it's still 17 million rows. I want to make a simple query in powerpivot now, but i can't find a group by maximum function.

my original table looks simplified like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]c[/TD]
[TD]a[/TD]
[TD]01/05/2014
[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]d[/TD]
[TD]01/05/2014[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]c[/TD]
[TD]a[/TD]
[TD]01/07/2014[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]c[/TD]
[TD]a[/TD]
[TD]01/06/2014[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]d[/TD]
[TD]01/04/2014[/TD]
[/TR]
</tbody>[/TABLE]

the new table should take the maximum of the date and group by the first three columns:

[TABLE="width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]c[/TD]
[TD]a[/TD]
[TD]01/07/2014[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]d[/TD]
[TD]01/05/2014[/TD]
[/TR]
</tbody>[/TABLE]

I can only find bigger than or smaller than functionality, no maximum.

Kind Regards
Mike

 

[theBardd]

What do you mean by you cannot connect to the database in Power Query, it supports DB2?

I have no knowledge of DB2, but does it support stored procedures, could you write one and connect to the database and use that SP in the query?

 

[TimRodman]

Assuming Excel 2013, do the following

1. Go into the Power Pivot window and go to Home -> PivotTable -> Flattened PivotTable.
2. In the PivotTable, drag your first three fields into the Rows area.
3. Go to POWERPIVOT -> Calculated Fields -> New Calculated Field in the Excel window.
4. Put in the following formula where DateColumnName is the name of your column that has dates in it:
   

   =MAX(DateColumnName)

5. Give your calculated field a name and drag it into the Values area in the PivotTable.

If you are on Excel 2010, it's basically the same steps except that Calculated Fields are called Measures.