Description

From Office 2010 onwards, Excel features a powerful new tool that helps you to create complex business reports. With PowerPivot you can create a PivotTable in an instant, which combines data from different data sources. These data sources do not necessarily need to be Excel lists; it is now easy to link an Excel list with data from an Access database and with a table that you can retrieve from an SQL server. Also, these lists do not need to be limited to the 1,048,576 available Excel rows. PowerPivot also has a range of new features you can add to the tables or the PivotTables to analyse data more efficiently.

Objectives

After completing this course:

• You can easily import different kinds of lists from various data sources
• You can create a PowerPivotTable with data from different lists
• You will know how to use DAX functions to analyse data more efficiently

Target group

This course is for anyone already familiar with PivotTables, but who would like to find out about an even more powerful analysing tool.

Prerequisites

You need to be familiar with entering functions without using the function wizard (which is not available in PowerPivot). In addition, you need to know the basics of PivotTables, such as: what is the purpose of a row heading, column heading, report filter, and a data value; how can I change a calculation in a PivotTable from SUM to AVERAGE ...

Content

Installing PowerPivot

• Installing PowerPivot
• Temporarily disabling PowerPivot
• Deleting PowerPivot

Importing data

• Linking an Excel table
• Importing an Excel table
• Adding data to a PowerPivot list
• Linking text files
• Linking data from other data sources (Access, SQL, ODBC …)

Relationships

• Defining relationships between tables

Working in the PowerPivot window

• Differences with the Excel datasheet
• The different data types
• Adding, deleting and hiding fields/columns
• Adding, deleting and hiding fields/columns
• Sorting in the PowerPivot window
• Filtering in the PowerPivot window

PowerPivotTables

• Creating a PowerPivotTable
• Differences with an Excel PivotTable
• Slicers

DAX functions (Data Analysis Expressions)

• Differences with Excel functions
• Difference between a measurement and a calculated column
• Overview of some important and useful DAX functions (Related, RelatedTable, Countrows, All, Calculate, AllExcept, Filter, CalculateTable, Distinct, Earlier, SumX, CountX, CountAX, MinX, MaxX,
• ClosingBalanceMonth, OpeningBalanceMonth, ClosingBalanceQuarter, OpeningBalanceQuarter, ClosingBalanceYear, OpeningBalanceYear, Edate

PivotTable or PowerPivot

• When to choose a PivotTable?
• When to choose a PowerPivot?

Duration

1 day(s)