Get to know Excel’s data-modelling and business-intelligence tools: Power Query, Power Pivot and Data Analysis Expressions (DAX).
Business intelligence combines methods and tools that collect data and turn it into useful information for decision-making.
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
Explore the syllabus
Introduction
• The Business Intelligence components
• Benefits of Business Intelligence tools
• When to use Power Query and Power Pivot
Power Query
• 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
Data Modeling
• 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
Power Pivot
• 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
Common DAX Functions
• 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
Basic-to-intermediate Excel skills are expected, including familiarity with functions and PivotTables. Power Pivot should be installed on your laptop.
For Excel users developing advanced analytical and data-modelling skills, including organisations whose datasets exceed a worksheet’s 1,048,576-row limit.
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)
A good place to start.
Tell us what you would like to learn or how you would like to collaborate. Our team will help with the next step.
Ready for your next step?
Let’s find the right training for you or your team.
