Get started with Power Pivot

• Install and set up Microsoft's Power Pivot add-in.
• Import various data sources into the Power Pivot Data Model.
• Import millions of records into the Power Pivot Data Model.
• Apply filters to import only a subset of the data.
• Understand the Data Model and the benefits of in-memory data storage.

Summarise data quickly and effectively in Power Pivot

• Create a Power Pivot PivotTable from multiple source data tables.
• Use related columns to side-step VLOOKUP formulas.
• Quickly add Slicers to PivotTables.

Open new analytical horizons with the DAX formula language in Power Pivot

• Create calculated columns with DAX functions.
• Add Measures (calculated fields) to a PivotTable.
• Learn how Measures are calculated.
• Use one Measure across multiple PivotTables.
• Discover new functions such as CALCULATE which turns any formula into a conditional calculation.
• Use time intelligence functions to calculate YTD for financial year.
• Compare values from various time periods with DATEADD.

Combine PivotTable power with formula flexibility

• Quickly convert a Power Pivot PivotTable to CUBE formulas.
• Write simple SUBTOTALs and reformat your report.
• Extract Slicer selections using CUBE formulas.
• Return KPI values with CUBE formulas.

Manipulate and consolidate data with Power Query

• Reshape and un-pivot data for Power Pivot analysis.
• Consolidate multiple sheets within a single file.
• Automate the consolidation of all files within a particular folder-no macros required!
• Allow user input with function parameters.

Construct an informative Power Pivot Cash Flow forecast

• Use separate heading tables to structure a report.
• Create subtotals correctly in a Power Pivot PivotTable.
• Use Slicers to toggle a report between thousands or millions.
• Create dynamic DAX Measures to calculate differently on certain report rows.
• Make an interactive waterfall chart for data insights.

Note that PivotTables and MS Query are standard features of all versions of Excel. The Power BI add-ins, although free, are available only for certain editions of Excel 2010 and 2013; please check the complete list of system requirements for specific details. Microsoft SharePoint is a for-purchase web application platform designed to facilitate collaboration and file management, and provide a central enterprise intranet portal. Although useful as a host for Power BI reports, SharePoint is not required and is used only in brief demonstrations during the course.

Prerequisites

You are familiar with PivotTables and constructing Excel formulas. Knowledge of criteria functions, such as SUMIF and DSUM may help you to get more out of the DAX formula language, but is not required.

• Be assured the advice you get is well-proven and utilised.
• Feel free to ask questions.
• Get time away from the office and constant interruptions.
• Enjoy a friendly environment where you don’t feel you’re holding others up.
• Instantly apply what you’ve learned on return to work.