Building BI with PivotTables

What’s in this course

Get The Course Files

• Download The Files

Intro - The Value of PivotTables

• Business Intelligence Lifecycle
• Why You Care About PivotTables
• Creating Your First PivotTable
• DEMO - Creating Your First PivotTable

Pivot Compliant Data Sets

• Required Data Layout
• Connecting to Your Source Data
• Issues Using Excel Ranges
• Using Excel Tables
• Building PivotTables Against Excel Tables
• Why Excel Tables are Better Than Ranges
• Using Data Direct From Databases
• Sourcing Data Direct From Databases to PivotTables
• Sourcing Data Direct From Databases to a Table
• Sourcing Data Direct From Databases to the Data Model

Controlling PivotTable Look & Feel

• Value Field Formats
• Modifying Column Names & Number Formats
• Report Layouts
• Changing PivotTable Layouts
• Grouping Data
• Grouping Data in PivotTables
• PivotTable Styles
• Creating Custom PivotTable Styles
• Sorting PivotTables
• Sorting Methods in PivotTables

Creating Compliant Data Sets

• Using Power Query to Get and Transform Your Data
• Cleaning Up A Text File With Power Query
• Appending Data Tables
• Simple Append Operations
• Consolidating Flat Files From Folder
• Consolidating A Folder of CSV Files
• Consolidating Excel Data
• Consolidating External Excel Data Files
• Get Data From The Active Workbook
• Consolidating Data From Within The Active Work File
• UnPivoting Data
• UnPivoting Simple Data Sets
• UnPivoting Data With Subheadings
• UnPivoting Subcategorized Data Sets
• Flattening Data Sets
• Flattening Tables With VLookup
• 6 Ways To Join Foundation
• Flattening Tables With Power Query - The Basics
• Join Types
• 6 Ways To Join With Power Query

Changing Aggregations

• Sum Count And Others
• Introduction To Changing Aggregations
• Show Values As
• Running Totals
• Difference And % Difference From
• Difference From X And % Difference From X
• Ranking Items
• Ranking Items in PivotTables
• Showing the Top/Bottom X Records
• Displaying the Top/Bottom X
• Calculated Fields
• Working with Calculated Fields
• Calculated Items
• Working with Calculated Items

Slicing and Filtering

• Filtering with Classic Controls
• Working with PivotTable Filter Fields
• Filtering With Slicers
• Working With Slicers
• Filtering The Timelines
• Working With The Timelines
• The Show Details Feature
• The Show Details Feature - DDEMO
• Keep Your Pivots Looking Nice
• Toggling PivotTable Options

Building Pivot Based Dashboard

• Conditional Formatting on Pivots
• Conditional Formatting DEMO
• Keeping Pivots in Sync
• Linking One Slicer to Multiple Pivots
• Tricks For Extracting Key Info
• Extracting Data From Pivots And Slicers
• Working With Pivot Charts
• Building Pivot Charts

Data Refresh & Security

• Driving PivotTable Refresh
• Refresh Options External DataSets
• Refresh Options Local DataSets
• How Secure Is Your Data?
• DEMO of Exposing Data