What is Power Query and Why it is the game changer?

11.1: Final Quiz Mandatory

12.1: Transition to Online Credibility & Power BI

Dhaval Patel

Data Entrepreneur | YouTuber | Ex - Bloomberg, Nvidia

I have 17 years of experience in Programming and Data Science working for big tech companies like NVIDIA and Bloomberg. I also run a famous YouTube channel called Codebasics where I pursue my passion for teaching. Codebasics is one of the top channels on YouTube when it comes to data science, machine learning, data structures, etc. I firmly believe that “Anyone can code” and I use analogies, simple explanations, and step-by-step storytelling to explain difficult concepts in such a way that even a high school student can understand them easily.

Hemanand Vadivel

Ex- Data Analytics Manager, 8+ Years in Europe, Microsoft Certified, Certified Supply Chain Professional

I’m a mechanical engineer who transitioned to a full time Data & Analytics manager in the UK & Germany by teaching myself Power BI, excel & anything else that was required to solve the problem. I have worked with complex data across Supply Chain, Sales, Marketing, Revenue Management, Finance and HR functions over the last 8 years to deliver effective solutions. To me, Analytics is an extra sensory organ that anyone can develop to become a superhuman at work. I have conducted several workshops and internal training to help non-technical people to become superhuman at work and now, really excited for this opportunity to share all that knowledge with you in this course.

Contents

1: Welcome to The Excel Experience

1.1: How Can Learning Excel Help You in Your Career?

1.2: Course Outline

2: Excel Basics: Getting Started

2.1: Peter Pandey’s Journey and Need to Learn Excel

2.2: Installing Excel: Windows / Mac

2.3: Getting Familiar With Excel

2.4: Getting More Familiar With Excel

2.5: Introduction to Formulas

2.6: Formula Behavior

2.7: Introduction to Tables

2.8: Introduction to Charts

2.9: Other Essential Features

2.10: Peter Gets His First Assignment From His Senior

2.11: Practice Exercise: Personal Expense Tracker

2.12: Quiz

2.13: Chapter Summary

3: Excel Basics: Cleaning & Combining Data

3.1: Intro to Movies Dataset

3.2: Cleaning Bad Data

3.3: Combining Data From Two Tables (VLOOKUP)

3.4: Combining Data From Two Tables (INDEXMATCH)

3.5: Combining Data From Two Tables (XLOOKUP)

3.6: Peter’s Request To Tony

3.7: Practice Exercise

3.8: Quiz

3.9: Chapter Summary

4: Excel Basics: Applying Business Maths & Statistics

4.1: Basic Maths to Business Metrics (Bollywood movies revenue avg)

4.2: Commonly Used Business Metrics (P&L)

4.3: Commonly Used Statistic Concepts (Mean, Median, Mode)

4.4: Commonly Used Statistic Concepts (Variance, Standard Deviation)

4.5: Commonly Used Statistic Concepts (Correlation)

4.6: Peter Gets to Solve a Real Time Problem

4.7: Practice Exercise

4.8: Quiz

4.9: Chapter Summary

5: Excel Basics+: Data Transformation Using Power Query

5.1: What is Power Query and Why it is the game changer?

5.2: Cleaning Data In Power Query

5.3: Merging Data in Power Query

5.4: Adding New Columns in Power Query

5.5: Loading Power Query Data to Excel

5.6: Peter Get’s His Hand Dirty

5.7: Practice Exercise

5.8: Quiz

5.9: Chapter Summary

6: Excel Basics+: Creating Business Reports Using Pivot Table & Power Pivot

6.1: Pivot Table Introduction: Apple Product Sales

6.2: Pivot Table For Movie Analytics

6.3: Creating a Report Using Pivot Table

6.4: Pivot Table Options & Formatting

6.5: Using Power Pivot & DAX For Powerful Business Reports

6.6: Adding Targets Using Data Modelling in Power Pivot

6.7: Breaking Down Complex Problems: Thinking Process of a Highly Paid Data Analyst

6.8: More Business Metrics and Conditional Formatting

6.9: VBA Basics & How Much VBA You Should Learn

6.10: Peter Practices On His Own

6.11: Practice Exercise

6.12: Quiz

6.13: I Need a Favour

6.14: Chapter Summary

7: Atliq Hardware & Problem Statement

7.1: Atliq’s Business Model

7.2: Usage of Excel & Business Requirement in Atliq

8: Excel Advanced: Sales Analytics

8.1: ETL (Extract, Transform and Load Data) in Excel I

8.2: ETL (Extract, Transform and Load Data) in Excel II

8.3: Business Report: Solution Design Thought Process

8.4: Creating Connections Among Tables Using Data Modelling

8.5: Adding a Date Table Using Power Query

8.6: Functional Knowledge: Sales

8.7: Sales Analytics: Creating Customer Performance Report

8.8: User Empathetic Report Design

8.9: Sales Analytics: Creating Market Performance vs Targets Reported

8.10: Practice Exercise

8.11: Quiz

8.12: Chapter Summary

9: Excel Advanced: Finance Analytics

This lecture is a part of Data Analytics Bootcamp 2.0.
Upgrade now to access enhanced modules.