Data Modelling and
Analysis with Power Pivot
Overview
This course is designed to equip participants with the skills and knowledge to effectively analyse and manipulate large datasets in Excel using Power Pivot feature, this course is ideal for professionals working with complex data who want to leverage the power of Power Pivot to enhance their data modelling and analysis capabilities.
Through the course, participants will learn how to create relationships between tables and design robust data models. They will also discover advanced techniques for organising and summarising data, such as using calculated columns. Additionally, participants will explore the DAX language to perform powerful calculations and create dynamic reports.
Course Objective
Upon successful completion of this course, you will have a solid understanding of Power Pivot and its capabilities, enabling them to efficiently analyse and visualize data, gain valuable insights, and make informed business decisions. You will be equipped with the skills to create interactive dashboards, perform complex data analysis, and present their findings effectively.
You will:
- Learn to use power pivot interface and menu options.
- Analyse more than 1 million rows of data.
- Create relationships between tables and data.
- Learn the principles of the DAX formula language to create powerful new calculations.
Who Should Attend
This course is designed for business users who want to analyse large data sets with Excel and create relationship between tables or create management reports. Also, individual who want to create interactive dashboards by using pivot tables and pivot chart.
Prerequisites
Participants who wish to enrol in this course should have a basic understanding of Pivot Tables and Pivot charts. Also, experience working with large datasets and data manipulation in Excel.
Training Calendar
Intake
Duration
Program Fees
Module
Module 1 - Introduction
- Welcome to analyse data with Excel Power Pivot
- Overview of Power Pivot course
- Activating power pivot in excel
- Loading data into power pivot
- Browse filter and sort power pivot data
Module 2 - Power Pivot Menu Options
- Home menu options
- The autosum function in Power Pivot
- Design menu options
- The advanced menu option
- Power Pivot menu in Excel
Module 3 - Creating Pivot Tables with Power Pivot Data
- Creating pivot tables
- Changing in method of calculation
- Pivot table calculations
Module 4 - Filtering with Slicers
- Pivot table filters and slicers
- Filtering pivot tables and pivot charts
- Creating hierarchies
Module 5 - Create Relationships with Power Pivot Data
- Create relationships to master data
- Create pivot tables with table relationships
Module 6 - DAX Calculated Columns
- Create calculated columns in DAX
- Data functions in Power Pivot
- Using the sort by option
- Create a date table
- Text and logical calculated fields
- Creating related calculated formulas
Module 7 - DAX Measures
- Review of calculated fields
- Power Pivot calculated fields
- The calculate formula
- Date measures examples
FAQs
General Questions:
-
Q: What is Power Pivot?
-
A: Power Pivot is an add-in for Microsoft Excel that allows you to perform powerful data analysis on large datasets. It enables you to import data from multiple sources, create relationships between tables, and use DAX formulas for complex calculations.
-
-
Q: What is this course about?
-
A: This course teaches you how to use Power Pivot to analyze large datasets, build data models, and create interactive reports directly within Excel. It covers data import, data modeling, DAX calculations, and report design.
-
-
Q: Who is this course for?
-
A: This course is designed for Excel users who want to go beyond basic spreadsheet functionality and perform more advanced data analysis. It’s beneficial for business analysts, data analysts, financial analysts, and anyone who works with large datasets in Excel.
-
-
Q: What are the prerequisites for this course?
-
A: A good understanding of basic Excel functionality, including formulas and PivotTables, is recommended. No prior experience with Power Pivot or DAX is assumed.
-
-
Q: What software do I need?
-
A: You’ll need Microsoft Excel with the Power Pivot add-in enabled. The specific Excel version might be specified by the training provider (e.g., Excel 2016, 2019, 365).
-
-
Q: How is the course structured?
-
A: The course structure varies depending on the provider. It might consist of in-person workshops, live online sessions, self-paced online modules, or a combination. Check the course details.
-
-
Q: How long is the course?
-
A: The duration varies depending on the scope and format. It can range from a few days to several weeks. Check the course details.
-
-
Q: Will I receive a certificate upon completion?
-
A: Yes.
-
Program Content & Skills:
-
Q: What specific topics are covered in the course?
-
A: Typical topics include installing and enabling Power Pivot, importing data from various sources, creating relationships between tables, understanding calculated columns and measures, writing DAX formulas, creating interactive reports and dashboards, and best practices for data modeling and DAX. Check the syllabus for a detailed list.
-
-
Q: What is DAX, and will I learn it in this course?
-
A: DAX (Data Analysis Expressions) is the formula language used in Power Pivot. It’s essential for creating custom calculations and measures. A Power Pivot course will cover DAX, but the depth of coverage might vary.
-
-
Q: Will I learn how to import data from different sources?
-
A: Yes, importing data from various sources (databases, text files, etc.) is a key part of the course.
-
-
Q: Will I learn how to create interactive reports and dashboards?
-
A: Yes, you’ll learn how to use PivotTables and PivotCharts to create interactive reports and dashboards based on your Power Pivot data model.
-
-
Q: Will I work on real-world examples and projects?
-
A: A good Power Pivot course will include hands-on exercises and real-world case studies to reinforce learning and make the skills applicable to your work.
-
Submit your interest today !
