Data Analysis With Excel Power Query, Data Model And Power Pivot Course Overview
This course introduces 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.
Learners will get hands-on with Power Query; a tool that extract, cleanse, transform, and load data from flat files, folders, databases and more. You’ll practice shaping, blending and exploring the project files, and create completely automated loading procedures with only a few clicks.
Next, learners will explore the Data Modeling and cover the fundamentals of database design and normalization (including table relationships, hierarchies and more). Learners will take a tour through Excel’s data model interface, and create their own relational database to analyze throughout the course.
Next, learners will 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). Learners will get to learn basic DAX syntax, then introduce some of the most commonly-used and powerful functions like CALCULATE, FILTER, SUMX and more.
Become an Excel power user and increase your job efficiency by equipping yourself with emerging data analysis skills.
This course is only for PC users with a copy of Excel that is compatible with Power Pivot. Power Pivot is an add-in for Excel. Not all Excel versions can support Power Pivot add-in. Learners must have one of these Excel versions.
Click the first link below to check the versions of Excel supporting Power Pivot. Click the second link to learn how to turn on the Power Pivot add-in.
At the end of the course, learners are able to:
- get themselves up and running with Excel’s data modeling and business intelligence tools
- use Power Query, Power Pivot and DAX to transform their workflow in Excel
This course is for anyone:
- who is an active Excel user who wants to learn more advanced data modeling and business intelligence tools
- is looking to become a power Excel user and enhance their analytics skill set
- whose Company has more than 1,048,576 rows of data (maximum rows in Excel worksheet) and wanting to go beyond the limits
This is a hands-on practical course. Trainer will walk through the topic step-by-step. Learners will be provided with exercise files on every topic to effectively apply what have been taught. A short Q & A session will be available after each topic.
- Must have Power Pivot installed (refer to Important Note under Course Description)
- Must possess basic to intermediate skills on MS Excel
- Must know how to work with Excel functions
- Well versed with Excel Pivot Table will be an added advantage, but not mandatory
- PC/Windows operating system laptop (personal laptop is preferred as some company laptops may have restriction in accessing internet and USB thumb drive)
- Laptop power adaptor
- A USB mouse
- A USB thumb drive or hard-disk
- Software requirement: Microsoft Excel version 2010-2019 or Office 365 with Power Pivot installed
- 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 and 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)
Our Dedicated Trainer
Alaster has been training adult learners in IT in both the private and public sectors since 1986. Alaster’s career before training centred around IT for over 12 years, as he held various roles within software development. He has earned himself a Microsoft Office Specialist (MOS) certification and he is an existing Trainer approved by People’s Association (PA). He was also a former Associate Trainer with the Civil Service College.
He specialises on training Microsoft Applications and Adobe Photoshop. Based on his vast experience and keeping abreast with the emerging trends, tools and technologies with the IT industry, especially software, he has extensively involved in developing several course outlines and course materials and conducting training to equip learners with skills in-demand or likely to be most important in the jobs of the future work.
Alaster constantly explore innovative and effective ways in imparting knowledge and engaging his learners at the same time. He also focuses his training on providing practical solutions to real-life problems, especially Excel. He is more than willing to help overcome various problems faced by his learners that arise in their workplace in applying their newly-acquired knowledge and skills any time after the course. He is well-known for his dedication, commitment and passion to training that makes him well-liked and appreciated by his learners.
All registration forms should be submitted at least 2 weeks before course commencement. Many courses have pre-requisites, which are intended to foster a learner’s success in the course. Be sure to check the necessary pre-requisites before registering.
Inclusive of 2 coffee breaks with refreshments and catered/buffet lunch
Call us for a group rebate when you register in a group of 6 or more
Absentees will be charged the full course fee
2 days, 9:00am – 5:00pm
(14 hrs, exclude lunch break)
If you have a group of 6 or more preparing for the same course, you can opt for your own “closed” class. Closed classes are perfect for corporate team training where group learning can make a difference.
Please note that closed class is not customised class.
For Company-Sponsored Learners: Click here to download registration form
For Self-Sponsored Learners:
Click here to download registration form
You can also register online here
For Government Ministries / Agencies on HRMS / ACE, you are unable to see our courses on the list. Please inform your Training Coordinator the Course Code, Course Title, Course Date and the Training Provider is Advanced Learning Singapore to upload into the system for registration
Details of hotel will be confirmed nearer the course start date
Complimentary car park coupon upon request (subject to availability and confirmed on a first come, first served basis)
ALS reserves the right to reschedule, postpone or cancel any course, change the trainer and venue at short notice and at its absolute discretion, due to insufficient enrolment, class size, availability and circumstances outside our control
More Courses By Alaster
Excel 100 Tips And Techniques Course (New & Exclusive)
Excel Data Cleansing And Building Techniques Course (New & Exclusive)