Course details
Overview
The new analytics tools in Excel make it easy to conduct manipulate and gain insights from big data. PowerPivot extends the use of pivot tables with a data model and offers improved data summarization and cross-tabulation and advanced calculations. Power Query retrieves data from a variety of sources can be cleaned up before being added to data model. Power View create interactive dashboards. Power Map is a 3D visualization tool to create map charts and interactive tours. These tools bring the power of advanced analytics to the average Excel user.
Lesson 1 - Understanding Power Pivot and the Power BI Tools
• What is Power Pivot, Power Query, Power View and Power Map
• Why use the Power BI Tools
• Excel versus Power Pivot
• Pivot Table Examples with Excel Data
• Power Pivot Examples with a Data Model
• Power View and Power Map Examples
Lesson 2 - Working with Data
• Working with an Excel List
• Using Vlookup to add Helper Columns
• Working with an Excel Table
• Cleaning up Tables
• Working with Normalized Tables
Lesson 3 - Importing Data into Power Pivot
• Understanding Acceptable Data Types
• Adding Excel Tables to Power Pivot
• Importing Access Tables
• Saving the File
• Adding and Maintaining Data in Power Pivot
Lesson 4 - Creating the Data Model
• What is a Data Model
• Understanding Key Fields
• Creating Relationships between Tables in the Model
• Managing Relationships
• Create and Use Linked Tables
• Create and Use Hierarchies
Lesson 5 - Using Calculations in Power Pivot
• Types of Calculations in Power Pivot
• Creating a Calculated Column
• Creating a Calculated Field (Measures)
• Implicit Calculated Field
• Explicit Calculated Field
• Understanding DAX Measure Rules and Best Practices
• Choosing Between Calculated Columns and Measures
• Creating a Key Performance Indicator (KPI)
Lesson 6 - Using Data Analysis Expressions
• Understanding Data Analysis Expressions (DAX) Formulas
• Where to use DAX formulas
• Understanding the DAX syntax
• How to create DAX formulas
• Types of operations you can perform with DAX
Lesson 7 - Working with DAX Formulas
• Working with Filter Function
• Working with Time Intelligence Functions
• Using Multiple Functions is a Formula
• Using Multiple Data Tables
Lesson 8 - Data Analysis with Pivot Tables and Pivot Charts
• Creating Pivot Tables
• Filtering Data using Slicers
• Add Visualizations to a Pivot Table
• Creating Pivot Charts
• Formatting Pivot Charts
• Using Multiple Charts and Tables
Lesson 9 - Working with Power View
• Visualizing Data with Power View
• Creating a Basic Report
• Making Improvements with Power View
• Creating Tables and Matrices
• Building Bar, Column and Pie Charts
• Constructing Line and Scatter Charts
• Producing Map-Based Visualizations
Lesson 10 - Building Interactive Reports with Power View
• Linking Visualizations in Power View
• Using tiles to Organize the Data
• Filtering Groups and Views
• Exposing the Dashboard
Lesson 11 - Loading and Transforming Data with Power Query
• Importing Data
• Transforming, cleansing and Filtering Data
• Merging and Shaping Data
• Grouping and Aggregating Data
• Inserting Calculated Columns
Lesson 12 - Visualizing Data with Power Map
• Preparing the Data for Mapping
• Creating a Map-Based Graph
• Creating Heat and Region Maps
• Adding Multiple Layers to a Map
• Analyzing Changes over Time
• Creating a Tour
Updated on 15 January, 2024 - Read Less
