Microsoft Excel – Introduction to Power Query, Power Pivot & DAX
This course introduces Microsoft Excel’s powerful data modeling and business intelligence tools: Power Query, Power Pivot, and Data Analysis Expressions (DAX).
Business intelligence (BI) is a collection of tools and processes that are used to gather data and turn it into meaningful information that people can use to make better decisions.
PREREQUISITES
No previous knowledge required
TARGET GROUP
Intermediate skills Excel
Duration
2 Days (14 hours)
Course Achievement
Certificate of Completion by Training Provider
At the end of the course, you will be able to:
• Have hands-on experience with Power Query
• Explore the Data Modeling and cover the fundamentals of database design and normalization (including table relationships, hierarchies and more)
• Use Power Pivot and DAX to explore and analyze the data model. Unlike traditional Pivot Table, Power Pivot allows you to analyze hundreds of millions of rows across multiple data tables, and create powerful calculated fields and measures using a formula language called Data Analysis Expressions (or “DAX” for short)
BENEFITS OF Microsoft Excel – Introduction to Power Query, Power Pivot & DAX TRAINING
Advanced data manipulation and transformation
Integration of multiple data sources
Enhanced data modeling and analysis
Improved data accuracy and efficiency
View Course Outline
• The Business Intelligence components
• Benefits of Business Intelligence tools
• When to use Power Query and Power Pivot
• Types of data connections
• The Query editor and tools
• Data loading options
• Basic table transformations
• Using various data types specific tools
• Index and conditional columns
• Pivot and Unpivot
• Grouping and aggregating data
• Working with workbook queries; merging, appending, deleting
• Connecting to a folder of files
• Refreshing the query
• Starting Data Model
• Changing to different views
• Database Normalization
• Data tables vs Lookup tables
• Primary and Foreign keys
• Working with tables relationship; creating, modifying, deleting
• Active vs Inactive relationships
• Relationship Cardinality
• Filter direction
• Hiding fields from client tools
• Defining hierarchies
• Normal Pivot Table vs Power Pivot
• Introduction to Data Analysis Expressions (DAX)
• Creating Power Pivot
• Calculated columns
• Introduction to DAX Measures
• Creating Implicit & Explicit Measures
• Managing Measures
• Calculated Columns vs Measures
• DAX syntax
• DAX operators
• Common DAX function categories
• Math & Stats functions
• Basic Logical functions
• Text functions
• Filter functions
• Iterator (“X”) functions
• Basic Date & Time functions
• Time Intelligence functions (optional)
Pre requisite & Methodology
You should have basic to intermediate skills on MS Excel, know how to work with Excel functions and Pivot Table. Your laptop should be installed with Power Pivot.
Who should attend:
Excel users who want to learn more advanced data modeling & business intelligence tools. Anyone looking to become a power Excel user and enhance their analytics skillset. Company that have more than 1,048,576 rows of data (maximum rows in Excel worksheet) and want to go beyond the limits.
Things to bring during the course:
• A laptop (preferable your personal laptop)
• Laptop power adaptor & A USB mouse
• A USB thumb drive or hard-disk
• Software requirement: Microsoft Excel version 2013-2019 or Office 365 (must have Power Pivot installed)
