This page has 0 threads | Add post

Courseware

Power Pivot course manual

Here are the chapters included in this course manual (we use exactly the same manuals for our classroom and online Power Pivot courses).

Click here to download a sample of the first two chapters of this Power Pivot manual, or review how to license our courseware.

Chapter 1 - GETTING STARTED WITH POWER PIVOT▼

Section	Title	Subsections	Page
1.1	What is Power Pivot?	The Power Pivot Workflow Enabling Power Pivot in Excel The Power Pivot Ribbon Tab	8
1.2	A Basic Power Pivot Example	Step 1 – Create an Excel Table Step 2 – Add the Table to the Data Model Step 3 – Edit the Data Model Step 4 – Create a Report Step 5 – Build the Pivot Table	10

Chapter 2 - DATA MODELS▼

Section	Title	Subsections	Page
2.1	Building Data Models	Our Example Data Model Our Example Data	14
2.2	Importing Data	Which Application to Use	16
2.3	Importing Data in Power Pivot	Step 1 - Choose to Import from Excel Step 2 – Choose the Excel File Step 3 – Choose and Rename Tables Step 4 – Apply Filters Step 5 – Finish Importing the Data	17
2.4	Power Pivot Views	The Data View The Diagram View	20
2.5	Editing Tables and Columns	Viewing Table Properties Deleting Columns Deleting Tables Renaming Tables and Columns Changing Column Data Types Formatting Columns Setting Default Aggregations Hiding Tables and Columns	21
2.6	Table Relationships	The Need for Relationships Detecting Relationships Creating Relationships Manually Deleting Relationships Managing Relationships	24
2.7	Refreshing a Data Model	Refreshing Data in Power Pivot Refreshing Data in Excel Refreshing Data Automatically	29

Chapter 3 - OTHER DATA SOURCES▼

Section	Title	Subsections	Page
3.1	Data Sources in Power Pivot		30
3.2	Importing from SQL Server	Step 1 – Pick the Server and Database Step 2 – Choose to use Tables or a Query Step 3 – Pick and Edit Tables Writing a SQL Query Designing a SQL Query	31
3.3	Importing from Access		34
3.4	Importing from Analysis Services	Connecting to Analysis Services Creating an MDX Query	35
3.5	Importing from Text Files		37
3.6	Importing from Reporting Services		38

Chapter 4 - PIVOT TABLES▼

Section	Title	Subsections	Page
4.1	Pivot Tables with Power Pivot	Creating a Pivot Table from Power Pivot Building a Pivot Table	39
4.2	Multiple Fields	Multiple Row and Column Fields Multiple Value Fields Date Fields	41
4.3	Pivot Table Values	Changing the Aggregation Applying Number Formats Alternative Ways to Show Values	43
4.4	Drill Down	Drilling Down Changing the Number of Rows	46
4.5	Formatting Pivot Tables	Pivot Table Styles Displaying Subtotals Grand Totals Layout Options Blank Rows Blank Cells	47
4.6	Sorting Pivot Tables	Sorting by Category Fields Sorting by Values Manually Sorting Items Sorting by Another Field	50
4.7	Filtering Pivot Tables	Basic Category Filters Multiple Category Fields Applying Label Filters Filtering by Values Filter Fields	52

Chapter 5 - CONDITIONAL FORMATTING▼

Section	Title	Subsections	Page
5.1	Conditional Formatting in Pivot Tables	Adding a Conditional Format Editing Conditional Formats Clearing Conditional Formats	55
5.2	Types of Conditional Format	Data Bars Icon Sets Top and Bottom Rules Above and Below Average Rules Custom Rules	58

Chapter 6 - PIVOT CHARTS▼

Section	Title	Subsections	Page
6.1	Introduction to Pivot Charts		61
6.2	Creating Pivot Charts	Creating a Pivot Chart in Power Pivot Changing the Pivot Chart Type Creating a Pivot Chart in Excel	62
6.3	Editing Pivot Charts	Elements of a Pivot Chart Showing and Hiding Chart Elements Adding, Removing and Moving Elements Chart Layouts	64
6.4	Formatting Pivot Charts	Chart Colours Chart Styles Formatting Elements	66
6.5	Sorting Pivot Charts	Displaying Field Buttons Sorting by Categories Sorting by Values	68
6.6	Filtering Pivot Charts	Basic Category Filters Adding Filter Fields Using Label Filters Filtering by Values	70
6.7	Expanding and Collapsing Fields		72

Chapter 7 - SLICERS▼

Section	Title	Subsections	Page
7.1	Introduction to Slicers		73
7.2	Creating Slicers	Inserting Slicers Positioning Slicers	74
7.3	Using Slicers	Applying and Removing Filters Selecting Multiple Items	75
7.4	Formatting Slicers	Slicer Styles Slicer Titles Filtering Slicers	76
7.5	Timelines	Creating Timelines Using a Timeline	77
7.6	Controlling Multiple Pivots	Changing Report Connections Changing Filter Connections	78

Chapter 8 - IMPORTING DATA WITH EXCEL▼

Section	Title	Subsections	Page
8.1	Import Tools in Excel	Get and Transform Data	79
8.2	Importing Excel Data	Choosing an Excel File to Import Selecting Tables to Import Loading the Selected Data The Queries Created	80
8.3	The Data Model	Building the Model What you can’t do	82
8.4	Importing SQL Server Data	Connecting to a SQL Server Database Getting Data with a SQL Query	84
8.5	Importing Other Files	Importing a Text File Importing from a PDF File	86
8.6	Importing Multiple Files	Choosing the Folder Combining the Files The Queries Created	88
8.7	Importing from Websites	Getting Data from a Web Table Creating Tables by Example Adding More Columns	90

Chapter 9 - TRANSFORMING DATA▼

Section	Title	Subsections	Page
9.1	Queries		93
9.2	Working with Queries	Opening the Power Query Editor The Power Query Screen Default Query Steps Viewing Data at Different Steps Editing a Query Step Deleting a Query Step Adding a New Step Applying Query Changes The Advanced Editor	94
9.3	Transforming Data	Changing Data Types Renaming Columns Removing Columns Removing Rows Sorting and Filtering Rows Splitting Columns Extracting Values Replacing Values Replacing Errors Duplicating Columns	99
9.4	Creating New Columns	Creating a Formula Creating a Column by Example	103
9.5	Conditional Columns		105

Chapter 10 - DAX IN POWER PIVOT▼

Section	Title	Subsections	Page
10.1	DAX in Power Pivot	Calculated Columns Measures Where Else is DAX Used? Getting Help within Power Pivot Other Sources of Help	106

Chapter 11 - BASIC CALCULATED COLUMNS▼

Section	Title	Subsections	Page
11.1	Calculated Columns	Key Features of Calculated Columns Creating a Calculated Column Calculated Column Properties Using Calculated Columns in Excel	108
11.2	Referencing Columns and Tables	Qualifying Column Names Table Names Referencing Columns in Other Tables The Related Function	110
11.3	Editing DAX Code	Multiple Lines and Indenting Comments Adding Line Numbers	112

Chapter 12 - WORKING WITH DATA TYPES▼

Section	Title	Subsections	Page
12.1	DAX Data Types	Viewing a Column’s Data Type	114
12.2	Working with Numbers	Basic Arithmetic Controlling the Calculation Order Safely Dividing Numbers Numeric Functions	115
12.3	Working with Text	Writing Text in Calculations Concatenating Text	118
12.4	Text Functions	Finding and Extracting Text Replacing Text Generating Text Converting and Formatting Text	119
12.5	Working with Dates	Entering Date and Time Values Returning the Current Date and Time Calculating Date and Time Values Calculating the Difference Between Dates Extracting Date Parts Formatting Dates	121

Chapter 13 - CONDITIONAL FUNCTIONS▼

Section	Title	Subsections	Page
13.1	The IF Function	Testing a Single Condition Comparison Operators The IN Operator Nesting IF Functions Combining Logical Tests The NOT Operator	124
13.2	Working with Blanks	Producing a Blank Blank Artihmetic Testing for Blanks	126
13.3	Testing for Errors	The ISERROR and IFERROR Functions Avoiding Error Functions	127
13.4	The SWITCH Function	A Simple SWITCH Function Logical Tests in a SWITCH Function	128

Chapter 14 - BASIC MEASURES▼

Section	Title	Subsections	Page
14.1	Introduction to Measures	Measures vs. Calculated Columns Implicit Measures	129
14.2	Creating Measures	Writing a Measure in Power Pivot Using Power Pivot’s AutoSum Tool Formatting Measures Creating Measures in Excel Editing Measures in Excel	130
14.3	Referencing Measures		133
14.4	Filter Context	What is Filter Context? How DAX Applies Filter Context	134

Chapter 15 - AGGREGATION FUNCTIONS▼

Section	Title	Subsections	Page
15.1	Aggregating Column Values	Basic Aggregation Functions Functions for Counting Dealing with Boolean Values	136
15.2	Aggregating Expressions	The AggregateX Functions	138
15.3	Iterators and Row Context	A Reminder of Filter Context Row Context in Iterator Functions The Final Result How to Spot Iterators	139

Chapter 16 - THE CALCULATE FUNCTION▼

Section	Title	Subsections	Page
16.1	Introducing the CALCULATE Function	Expressions in the CALCULATE Function	141
16.2	Adding New Filters	Basic Filter Expressions Adding Mutliple Filters Filter Arguments and Filter Context Multiple Columns in Filter Arguments	142
16.3	Replacing Filters	Replacing an Existing Filter Comparing Differently Filtered Measures Dealing with Blank Values	145
16.4	Keeping Filters	The Problem with the Default Behaviour The KEEPFILTERS Function Using the VALUES Function	147
16.5	Removing Filters	Removing a Filter from a Column Comparing Filtered and Unfiltered Values Removing Filters from a Table	149
16.6	Special Filter Removal Functions	The ALLEXCEPT Function The ALLSELECTED Function	151

Chapter 17 - VARIABLES▼

Section	Title	Subsections	Page
17.1	Introduction to Variables	Using Variables in Measures	153
17.2	How Variables are Evaluated	Lazy Evaluation DAX Variables are Constants	154
17.3	Debugging with Variables	Returning Different Variables	155
17.4	Nesting Variables	Variables in Functions	156

Chapter 18 - THE FILTER FUNCTION▼

Section	Title	Subsections	Page
18.1	Introduction to the FILTER Function	A Basic FILTER Example Using the CALCULATE Function How CALCULATE and FILTER are Related Using Multiple Filters Using Variables	157
18.2	FILTER vs. CALCULATE	Referencing Multiple Fields Referring to Measures Replacing Filters	159
18.3	The CALCULATETABLE Function	Using the CALCULATETABLE Function	161

Chapter 19 - FILTERS AND RELATIONSHIPS▼

Section	Title	Subsections	Page
19.1	Relationships and Filter Direction	Filter Direction Filter Direction and Measures The CROSSFILTER Function Multiple CROSSFILTER Functions	162

Chapter 20 - CONTEXT TRANSITION▼

Section	Title	Subsections	Page
20.1	What is Context Transition?	Row and Filter Context	165
20.2	Context Transition in Calculated Columns	Row Context in Calculated Columns Performing Context Transition Implicit Context Transition The RELATEDTABLE Function	166
20.3	Context Transition in Measures	Row Context in Measures Context Transition in Measures The Effect of Filter Context Removing Filters	168
20.4	Ranking Values	The RANKX Function Ranking in Calculated Columns Context Transition in Calculated Columns Ranking in Measures	171

Chapter 21 - CALENDAR TABLES▼

Section	Title	Subsections	Page
21.1	Introduction to Calendars	The Date Column	173
21.2	Creating a Calendar Table	Adding a Date Table Marking as a Date Table Creating Relationships Using a Calendar in Pivot Tables	174
21.3	Customising a Calendar	Adding Columns Sorting Columns Hiding Columns Creating Hierarchies Updating the Date Range Saving a Custom Calendar	176
21.4	Multiple Date Fields	Using Multiple Calendars Using a Single Calendar Changing the Active Relationship The USERELATIONSHIP Function	179
21.5	Special Dates		182

Chapter 22 - TIME INTELLIGENCE▼

Section	Title	Subsections	Page
22.1	Time Intelligence	Calendar Tables	183
22.2	General Time Intelligence Functions	Using the DATEADD Function How DATEADD Works Using the DATESINPERIOD Function Using the Current Date Using Specific Dates	184
22.3	To Date Calculations	Returning Date Ranges Calculating Running Totals Total To Date Functions Easier Running Total Calculations Specifying Year End Dates Calculating Life to Date Values	187
22.4	Next and Previous Periods	Next and Previous Period Functions Comparing Entire Previous Years Comparing Parts of Previous Years	190
22.5	Period Start and End Dates	Period Start and End Functions Start and End Dates Opening and Closing Balances First and Last Non-Blank Dates First and Last Non-Blank Values Non-Blank Opening Balances	193
22.6	Moving Averages	Simple Moving Averages Simple Moving Averages of Sums	197

Chapter 23 - KPIS▼

Section	Title	Subsections	Page
23.1	What are KPIs?	Structure of a KPI	199
23.2	Creating a KPI	Creating the Measures Creating the KPI Showing the KPI in a Pivot Table Editing the KPI	200
23.3	Absolute Targets		202

If you book a place on one of our courses you will automatically get access to the relevant courseware manual online, and also have the option to receive a paper copy of it.

This page has 0 threads

Add a new post

Choose software

Power Pivot course manuals