Course description
Business intelligence (BI) involves a set of theories, methodologies, architectures, and technologies to transform raw data into meaningful and useful information for business analysis purposes. To this end, Microsoft Excel’s Power Pivot offers an agile and cost effective approach to BI. In this course, you will learn how to use Excel and its Power Pivot tools to mine large amounts of sophisticated data to provide better data analysis, business insights and support informed decisions. Additionally, you will learn how to employ PivotTables, PivotCharts and VBA (Visual Basic for Applications) to present critical information to senior management through dashboards, enabling them to see a complete picture for a given initiative, project, or their organization.
Upcoming start dates
Who should attend?
Training content
Chapter 1: Thinking Like a Database
- Exploring the Limits of Excel and How Databases Help
- Scalability
- Transparency of analytical processes
- Separation of data and presentation
- Getting to Know Database Terminology
- Databases
- Tables
- Records, fields, and values
- Queries
- Understanding Relationships
Chapter 2: Introducing Power Pivot
- Understanding the Power Pivot Internal Data Model
- Activating the Power Pivot Add‐In
- Linking Excel Tables to Power Pivot
- Preparing Excel tables
- Adding Excel Tables to the data model
- Creating relationships between Power Pivot tables
- Managing existing relationships
- Using the Power Pivot data
- viii Excel Power Pivot & Power Query For Dummies
Chapter 3: The Pivotal Pivot Table
- Introducing the Pivot Table
- Defining the Four Areas of a Pivot Table
- Values area
- Row area
- Column area
- Filter area
- Creating Your First Pivot Table
- Changing and rearranging a pivot table
- Adding a report filter
- Keeping the pivot table fresh
- Customizing Pivot Table Reports
- Changing the pivot table layout
- Customizing field names
- Applying numeric formats to data fields
- Changing summary calculations
- Suppressing subtotals
- Showing and hiding data items
- Hiding or showing items without data
- Sorting the pivot table
- Understanding Slicers
- Creating a Standard Slicer
- Getting Fancy with Slicer Customizations
- Size and placement
- Data item columns
- Miscellaneous slicer settings
- Controlling Multiple Pivot Tables with One Slicer
- Creating a Timeline Slicer
Chapter 4: Using External Data with Power Pivot
- Loading Data from Relational Databases
- Loading data from SQL Server
- Loading data from Microsoft Access databases
- Loading data from other relational database systems
- Loading Data from Flat Files
- Loading data from external Excel files
- Loading data from text files
- Loading data from the Clipboard
- Loading Data from Other Data Sources
- Refreshing and Managing External Data Connections
- Manually refreshing Power Pivot data
- Setting up automatic refreshing
- Preventing Refresh All
- Editing the data connection
Chapter 5: Working Directly with the Internal Data Model
- Directly Feeding the Internal Data Model
- Adding a New Table to the Internal Data Model
- Removing a Table from the Internal Data Model
- Creating a New Pivot Table Using the Internal Data Model
- Filling the Internal Data Model with Multiple
- External Data Tables
Chapter 6: Adding Formulas to Power Pivot
- Enhancing Power Pivot Data with Calculated Columns
- Creating your first calculated column
- Formatting calculated columns
- Referencing calculated columns in other calculations
- Hiding calculated columns from end users
- Utilizing DAX to Create Calculated Columns
- Identifying DAX functions that are safe for calculated columns
- Building DAX‐driven calculated columns
- Referencing fields from other tables
- Understanding Calculated Measures
- Creating a calculated measure
- Editing and deleting calculated measures
- Free Your Data With Cube Functions
Chapter 7: Publishing Power Pivot to SharePoint
- Understanding SharePoint
- Understanding Excel Services for SharePoint
- Publishing an Excel Workbook to SharePoint
- Publishing to a Power Pivot Gallery
- Exploring the Power Pivot Gallery
- Refreshing data connections in published Power
- Pivot workbooks
- Part II: Wrangling Data with Power Query
Chapter 8: Introducing Power Query
- Installing and Activating a Power Query Add‐In
- Power Query Basics
- Starting the query
- Understanding query steps
- Refreshing Power Query data
- Managing existing queries
- Understanding Column‐Level Actions
- Understanding Table Actions
Chapter 9: Power Query Connection Types
- Importing Data from Files
- Getting data from Excel workbooks
- Getting data from CSV and text files
- Getting data from XML files
- Getting data from folders
- Importing Data from Database Systems
- A connection for every database type
- Getting data from other data systems
- Walk‐through: Getting data from a database
- Managing Data Source Settings
Chapter 10: Transforming Your Way to Better Data
- Completing Common Transformation Tasks
- Removing duplicate records
- Filling in blank fields
- Concatenating columns
- Changing case
- Finding and replacing specific text
- Trimming and cleaning text
- Extracting the left, right, and middle values
- Splitting columns using character markers
- Pivoting and unpivoting fields
- Creating Custom Columns
- Concatenating with a custom column
- Understanding data type conversions
- Spicing up custom columns with functions
- Adding conditional logic to custom columns
- Grouping and Aggregating Data
Chapter 11: Making Queries Work Together
- Reusing Query Steps
- Understanding the Append Feature
- Creating the needed base queries
- Appending the data
- Understanding the Merge Feature
- Understanding Power Query joins
- Merging queries
Chapter 12: Extending Power Query with Custom Functions
- Creating and Using a Basic Custom Function
- Creating a Function to Merge Data from Multiple Excel Files
- Creating Parameter Queries
- Preparing for a parameter query
- Creating the base query
- Creating the parameter query
- Table of Contents xi
- Part III: The Part of Tens
Chapter 13: Ten Ways to Improve Power Pivot Performance
- Limit the Number of Rows and Columns in Your Data
- Model Tables
- Use Views Instead of Tables
- Avoid Multi‐Level Relationships
- Let the Back‐End Database Servers Do the Crunching
- Beware of Columns with Non‐Distinct Values
- Limit the Number of Slicers in a Report
- Create Slicers Only on Dimension Fields
- Disable the Cross‐Filter Behavior for Certain Slicers
- Use Calculated Measures Instead of Calculated Columns
Chapter 14: Ten Tips for Working with Power Query
- Getting Quick Information from the Workbook Queries Pane
- Organizing Queries in Groups
- Selecting Columns in Queries Faster
- Renaming Query Steps
- Quickly Creating Reference Tables
- Copying Queries to Save Time
- Setting a Default Load Behavior
- Preventing Automatic Data Type Changes
- Disabling Privacy Settings to Improve Performance
- Disabling Relationship Detection
Quick stats about BBLN?
In operation for over 20 years, Building Blocks takes a consultative approach to customer engagement to deeply asses the needs. We offer a variety of options for consideration.
All of our instructors have a minimum of 20 years of teaching experience using adult learning theory.
We can proudly say that our customers routinely say we exceeded their expectations and in doing so, we offer many benefits that are included in the service and pricing.
Contact this provider
Building Blocks Learning Network
Building Blocks Learning Network (Building Blocks) is a training company with over 20 years of experience in Adult Education. Our specific expertise includes professional development in the areas of Executive Leadership, Management and Leadership, Coaching, Policy Development, How Government Works,...