Practical Excel 2016: PowerPivot & Advanced Business Intelligence Tools for the Workplace (ILT)
COURSE DESCRIPTION
This course takes up where the Optima Train Excel 2016 Pivot Tables and Pivot Charts course leaves off. It all revolves around the fairly new suite of Microsoft “Power” tools, often referred to as Power BI. The course has three primary themes. First, it teaches you a number of methods for importing data (from a database, text files, or other sources) into Excel. The primary emphasis is on Power Query. Second, it devotes considerable time to the Excel Data Model and Power Pivot for analyzing data with pivot tables. These take traditional pivot tables to a whole new level. Third, it shows how Power View and Power Map can be used to create insightful reports and maps with very little work.
Category: Office Productivity/Microsoft
Duration: 1-2 Days
What are the requirements?
- Microsoft Office 2016 (Professional Plus or higher)
What am I going to get from this course?
- An understanding of how the four components of Microsoft’s Power BI suite (Power Query, Power Pivot, Power View, and Power Map) work together to provide amazing data analysis possibilities, all within Excel
- How to import data from various data sources into Excel, both with older tools and the newer Power Query tools, with emphasis on the latter
- An understanding of the relationship between the Excel Data Model and Power Pivot
- An understanding of how Power Pivot adds significant power to Excel’s existing data analysis tools
- How to use the Power Pivot user interface, and how to create pivot tables with Power Pivot
- How to use the DAX language in Power Pivot to create calculated columns and measures, and why DAX is so powerful
- How to create named sets, hierarchies, and KPIs (key performance indicators) with Power Pivot
- How Power View and Power Map can be used to create insightful reports and maps with very little work
What is the target audience?
- Any competent Excel users who want to be on the leading edge with Excel’s latest and most powerful data analysis tools
Lesson Plan
| Introduction to Course | |
| Welcome to PowerPivot and Advanced BI Tools Course | |
| Power Query and Other Tools for Importing Data | |
| Overview of Importing Data into Excel and Power Query | |
| Introduction to Relational Databases | |
| Introduction to Queries | |
| Introduction to Power Query | |
| More Power Query Possibilities | |
| Power Query User Interface | |
| Importing Data from the Web | |
| Using Microsoft Query | |
| Importing Azure DataMarket Data | |
| Power Pivot for Analyzing Data | |
| Overview of Pivot Table Technologies | |
| Introduction to OLAP | |
| Understanding the Excel Data Model Versus Power Pivot | |
| Pivot Tables Based on a Data Model 1 | |
| Advantages of the Excel Data Model over Regular Excel Pivot Tables | |
| Advantages of Power Pivot over the Excel Data Model | |
| Migrating to Power Pivot for Excel 2016 | |
| Help in Power Pivot | |
| Power Pivot User Interface | |
| Getting Data into Power Pivot | |
| Creating Relationships in Power Pivot | |
| Building Pivot Tables in Power Pivot | |
| More on Building Pivot Tables in Power Pivot | |
| Working in Data View in Power Pivot | |
| Introduction to DAX in Power Pivot | |
| Learning about DAX Functions | |
| Creating Calculated Columns with DAX | |
| Using the RELATED and RELATEDTABLE Functions | |
| Whether to Denormalize an Excel Data Model | |
| Creating Measures with DAX | |
| One Huge Benefit of DAX Measures | |
| Understanding Pivot Table Evaluation | |
| Many-to-Many Relationships in Power Pivot | |
| Counting Distinct Values in Power Pivot | |
| Using the CALCULATE Function 1 | |
| Using the CALCULATE Function 2 | |
| Using Date Functions in Power Pivot | |
| Using a Parameter Table in Power Pivot | |
| Hierarchies in Power Pivot | |
| Named Sets in Power Pivot | |
| KPIs in Power Pivot | |
| Cube Functions in Power Pivot | |
| Power View and Power Map for Creating Reports | |
| Introduction to Power View | |
| Power View User Interface | |
| More Power View Possibilities | |
| Introduction to Power Map | |
| Power Map User Interface | |
| More Power Map Possibilities | |
| Conclusion to Course | |
| Conclusion to PowerPivot and Advanced BI Tools Course |
Course Reviews
No Reviews found for this course.