Advanced MS-EXCEL Bootcamp - Power Query & Power Pivot
About the Course:
It is said that around 80 - 85% of the time is spent on data cleaning & data transformation by Data Scientists, Data Engineers, and Data Analysts. With the 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, who has trained 15,000+ worldwide 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 8-hour live virtual training on MS Excel - Advanced Level, you will learn how to use Excel Power Query to CONNECT and 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 the 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 Dat
- Connecting Excel to other data sources
- Introduction to Power Pivo
- 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 Exce
- Excel users who want to learn how to use the Power Pivot Data Mode
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.
Introduction to Power Query
- What is Power Query?
- Connecting Dataset to Power Query
- Perform Transformations
- Conditional IF
Advanced Transformation
- Introduction to Advanced Transformations
- Merge
- Append Data
- Custom calculations
- Group by calculations
Cleanse Data
- Introduction to Cleanse Data
- Query Editor Diagnostics
- Cleanse Data
Connect to other Data Sources
- 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
Introduction to Power Pivot
- Overview of Excel Power Pivot
- Activating Excel Power Pivot
- Loading data into the Data Model
- Browsing, Filtering, and Sorting
Power Pivot and Pivot Tables
- Introduction to analyzing data with Pivot Table
- Creating Pivot Tables
- Slicing and filtering data with Pivot Tables
- Two Pivot Tables and Charts
Creating Calculated Columns
- Introduction to Calculated Columns
- Date and time functions
- Formatting Month & Weekday Names
- Creating a Date Table
- IF function
- SWITCH function
Introduction to Measures
- Various aggregations
- CALCULATE function
- ALL and ALL EXCEPT functions
- Time Intelligence Measures
Additional Measures
- SUMX function
- RANKX function
- Customer Segmentation
Relationships
- Introduction to Relationships
- Creating Relationships
- Relationship Calculations
Sets and KPIs
- Introduction to KPIs and Sets
- Creating Sets
- Creating Hierarchies
- Creating KPIs