Advanced MS-EXCEL Bootcamp - Power Query & Power Pivot
-
Training TypeLive Training
-
CategoryMS Excel
-
Duration12 Hours
-
Rating4.8/5
Course Introduction
About the Course
It is said that around 80 – 85% of time is spent on data cleaning & data transformation by Data Scientists, Data Engineers & Data Analysts. With Power Query tool, acquiring, transforming, and preparing data becomes faster and simpler. Microsoft Excel’s Power Query tool is the biggest feature Microsoft has added to Excel since Pivot Tables. It will save loads of time as you take advantage of this powerful feature.
Training will be given by Microsoft Certified Excel & Power BI Professional & Microsoft Certified Trainer, has trained 15,000+ world-wide Industry Professionals, University Professors, and Students in MS Excel, MS Power BI, Tableau and Google Looker Studio. He has a reputation for delivering excellent, logically structured courses that are easy to follow and get the point across without wasting learners’ time.
In this brand new 12-hour live virtual training on MS Excel – Advanced Level, you will learn how to use Excel Power Query to CONNECT & TRANSFORM data in Excel and perform ADVANCED TRANSFORMATION like Merging, Appending, Unpivoting, Grouping and Custom Calculations. Also, you will learn how to create DATA MODELS in Excel and perform SOPHISTICATED calculations such as Time Intelligence, Filtered Data and Segmentation. At completion of this training, you will have mastered the most popular & widely used Excel tools and come out with confidence to complete any Excel tasks with efficiency and grace.
Course Objective
Introduction to Power Query
Basic & advanced Transformations
Cleansing Data
Connecting Excel to other data sources
Introduction to Power Pivot
Power Pivot & Pivot Tables
Creating Calculated Columns & Measures
Additional Measures & Relationships
Sets & KPIs
Who is the Target Audience?
Excel users who want to learn how to import and transform data in Excel
Excel users who want to learn how to use the Power Pivot Data Model
Basic Knowledge
You should be able to work with data in Excel and do basic reporting. Also, you should have Excel 2016 or above version.
Available Batches
| 13 May 2024 | Mon - Wed ( 3 Days) | 11:00 AM - 03:00 PM (Eastern Time) |
Pricing
Require a Different Batch?
Request a Batch For
-
What is Power Query?
-
Connecting dataset to Power Query
-
Perform Transformations
-
Conditional IF
-
Introduction to Advanced Transformations
-
Merge
-
Append Data
-
Custom calculations
-
Group by calculations
-
Introduction to Cleanse Data
-
Query Editor Diagnostics
-
Cleanse Data
-
Loading data from .csv file
-
Loading data from the Web
-
Loading data from XML files
-
Loading data from JSON
-
Loading data from SQL Server
-
Loading data from Tables
-
Overview of Excel Power Pivot
-
Activating Excel Power Pivot
-
Loading data into the Data Model
-
Browsing, Filtering and Sorting
-
Introduction to analysing data with Pivot Table
-
Creating Pivot Tables
-
Slicing & Filtering data with Pivot Tables
-
Two Pivot Tables and Charts
-
Introduction to Calculated Columns
-
Date & Time functions
-
Formatting Month & Weekday Names
-
Creating a Date Table
-
IF function
-
SWITCH function
-
Various aggregations
-
CALCULATE function
-
ALL and ALLEXCEPT functions
-
Time Intelligence Measures
-
SUMX function
-
RANKX function
-
Customer Segmentation
-
Introduction to Relationships
-
Creating Relationships
-
Relationship Calculations
-
Introduction to KPIs and Sets
-
Creating Sets
-
Creating Hierarchies
-
Creating KPIs