The Roadmap
to Data
Analyst
Module 2025
www.theknowledgecastle.com
Content
Welcome to the Guide 03
What a Data Analyst Actually Does 04
SQL 05
PowerBi 20
Excel 34
VBA 42
Python 53
Useful Data Analyst resources 72
Conclusion 73
1. Welcome to the
Guide
Welcome to A Data Analyst's Career Guide - 2025!
Whether you are an aspiring data analyst, a career
switcher, or looking to advance in your current
analytics role, this guide will provide you with all the
essential information you need to succeed.
The data analytics
industry is evolving
rapidly, and with
increasing demand for
skilled professionals,
understanding the
field's expectations
and opportunities is
crucial. This guide will
help you navigate the
landscape and set
yourself up for
success.
Kanav Taneja
Founder
2. What a Data Analyst
actually does
A data analyst plays a vital role in organizations by
interpreting data, identifying trends, and providing
actionable insights. Their work involves:
Collecting, cleaning, and
analyzing data (data
wrangling accounts for 60-
70% of an analyst's time)
Identifying patterns and
trends to improve decision-
making
Creating reports and
dashboards to visualize data
effectively
Assisting in data-driven
decision-making by
presenting insights to
leadership teams
Using predictive modelling to
forecast business outcomes
and trends
Implementing A/B testing to
optimize marketing and
product strategies
Understanding and
managing big data platforms
to scale analytical operations
Chapter 1
Introduction to databases
SQL and MySQL
01 Why SQL?
02 Why MySQL?
03 Introduction to databases
Chapter 2
SQL Theory
01 SQL as a declarative language
02 Data definition language (DDL)
03 SQL keywords
04 Data manipulation language (DML)
05 Data control language (DCL)
06 Transaction control language (TCL)
Chapter 3
Basic database
terminology
01 Relational database essentials
02 Databases vs spreadsheets
03 Database terminology
04 Relational schemas - Primary key
05 Relational schemas - Foreign key
06 Relational schemas - Unique key and null
Chapter 4
First steps in SQL
01 Creating a Database - Part I
02 SQL files
03 Introduction to data types
04 ü String data types
05 ü Integers
06 ü Fixed and floating-point data types
07 ü Other useful data types
08 Creating a table
Chapter 5
MySQL constraints
01 Primary Key Constraint
02 Foreign Key Constraint
03 Unique Constraint
04 Default Constraint
05 Not Null Constraint
Chapter 6
SQL Select Statement
01 Select From
02 Where
03 And, Or, Operator Precedence
04 In – Not in, Like - Not Like
05 Wildcard Characters
06 Between – And, Is Not Null – Is Null
07 Select Distinct
Chapter 7
Introduction to Aggregate
Functions
01 Order By
02 Group By
03 Using Aliases
04 Having
05 Where Vs Having
06 Limit
Chapter 8
SQL Insert Statement
01 The Insert Statement
02 Inserting data into a new Table
Chapter 9
SQL Update Statement
01 TCL’s Commit and Rollback
02 The Update Statement
03 Rollback
Chapter 10
SQL Delete Statement
01 Delete Statement
02 Drop
03 Truncate
04 Drop vs Truncate
Chapter 10
SQL Delete Statement
01 Delete Statement
02 Drop
03 Truncate
04 Drop vs Truncate
Chapter 11
Aggregate Functions
01 Count(), Sum()
02 Min() & Max()
03 Avg()
04 Round()
05 Coalesce()
06 Isnull() & Coalesce()
Chapter 12
SQL Joins
01 Introduction to Joins
02 Inner Join, Left, Right & Outer Join
03 Duplicate Records
04 Join & Where Used together
05 Cross Join
06 Aggregate Functions with Join
07 Join more than 2 tables and self join
08 Union vs Union All
Chapter 13
SQL Subqueries
01 SQL subqueries with in nested inside where
02 SQL subqueries nested in select & from
Chapter 14
Advanced SQL Topics
01 Views
02 MySQL Indexes
03 The Case Statement
04 MYSQL Triggers
05 Row_Num()
06 Rank()
_____________________________________
Congratulations for Completing the SQL
_____________________________________
Chapter 1
DATA LITERACY
01 What is Data Literacy
02 Definition of data
03 Finding your next client
04 Class of Variables
05 Nominal Data
06 Ordinal Data
Chapter 2
Chart Types in Power BI
01 Bar Chart, Line Chart
02 Stacked Bar Chart, Histogram
03 Heat maps, Tree Maps
04 Bullet Graph, Combo Charts
05 Scatter Plot, Cross-Tab
06 Map Visuals, Waterfall Chart
07 Table and Matrix Visuals
Chapter 3
Data Management
with Power Query
01 Planning Power BI Project
02 Power Query - Combining and Cleaning
03 Power Query - Transforming Data
Chapter 4
Introduction to
PowerBi
01 What is Power BI
02 Quick Tour of Power BI
03 Download Files & Important Course Info
Chapter 5
Create Your First
Power BI Report for
Knowledge Castle
(USA)
01 First Project Overview
02 Loading Data for Analysis
03 Creating a Data Model
Chapter 5
Create Your First
Power BI Report for
Knowledge Castle
(USA)
04 Exploring Visuals in Power BI
05 Filters and Slicers
06 Creating Quick Measures
Chapter 6
Dashboard Designing
01 Designing Report Layout (Theme & Draft)
02 Getting Started on our Dashboard
03 Sync Slicers & Edit Interactions between Visuals
04 Drill Down & Line Charts
05 Custom Hierarchies for Drill down
06 Adding Custom Visuals like Word Cloud
07 Artificial Intelligence Visuals – Smart Narrative
08 Artificial Intelligence Visuals –QA
Chapter 6
Dashboard Designing
09 Top N Filter & Conditional Formatting
10 Dynamic Card Titles with Conditional Formatting
11 Customize Tooltips & Analyze Data
12 Analytics Pane to add More Analysis to Visuals
13 Adding Page Navigation and Back Buttons
14 Challenge & Final Touches
15 Quiz
Chapter 7
Publishing Dashboard
01 Publishing and Exploring Report in Power BI Service
02 Create a Dashboard from your Report
03 Mobile Dashboard
04 Refreshing the Dataset in Power BI Desktop
05 Connect to a published Dataset
06 Quiz
07 Key Takeaways
_____________________________________
Congratulations for Completing the Fast Lane
_____________________________________
Chapter 8
Data Analysis with
DAX & Power Pivot
01 DAX Deep dive
02 Power Pivot
03 Calculation groups, dimension metrics
04 dynamic ranking measures
05 dynamic row-level security
06 Time intelligence patterns in Power BI
07 Performance testing
Chapter 9
Story telling with
Power BI
01 Report Tooltips
02 Conditional formats
03 Text commentary with DAX
04 Play Axis for scatter charts, Pulse chart
05 Analytics Pane
06 Quick Insights
07 Dimensional modeling with Power Query
Chapter 9
Story telling with
Power BI
08 Reference vs. Duplicate queries
09 Generating calendar tables
10 Data modeling with multiple fact tables
11 Measure development
12 Dashboard design and implementation
13 Qiiz
14 Key Takeways
Chapter 10
Executive
Dashboard-I
01 Part 1 – Report goals and design
02 Part 2 – Data Modeling and Measures
03 Part 3 – Business View with KPIs & forecast
04 Part 4 – Business View contd
05 Part 5 – Finance Department View with What-if analysis
Chapter 11
Executive
Dashboard-II
01 Creating a timeline of funded amounts
02 Creating a Bar chart for sector
03 Creating a pie chart of split by Gender
04 Action: Filter, Highlight
05 PowerBi Quiz
_____________________________________
Congratulations for Completing the Powerbi
_____________________________________
Chapter 1
Basic Excel
01 Introduction
02 Excel Course Structure
03 Navigate Excel Ribbons
04 Open New And Save a file
05 Enter text in workbook
06 Formatting the text
Chapter 2
Working with Excel
01 Autofill Rewrite & Edit
02 Copying & pasting
03 Change Borders & background Colors
04 Mege Cells & Find, replace
05 Data Type
06 Working with Sheets
07 Rows And Columns
Chapter 3
Working with Data in
Excel
01 Inserting & deleting Rows
02 Freeze Panes
03 Formulas & reference Cells
Chapter 4
Formulas in Excel
01 Formulas Rule
02 Name manager
03 Lock Cells
Chapter 5
Working with Excel
Worksheets &
Worksbooks
01 refer Other worksheets & Books
02 Ciscular Reference
Chapter 6
Advanced Formulas
01 Vlookup
02 Index & Match
03 Power Functions
Chapter 7
Logical Functions
01 errors
02 If & Logical
03 Indirect
04 Text Functions
05 Data Validation
Chapter 8
Advanced Excel
01 Offset
02 Indirect & Offset together
03 Sorting
04 Filters
05 Pivot Tables
06 Conditonal Formatting
07 Charts
theknowledgecastle
Microsoft qualified institute
VBA
Module
Learn essential Excel and VBA, with real world
applications.
Created by
Kanav Taneja
www.theknowledgecastle.com
Chapter 1
INTRODUCTION to
VBA
01 Introduction
02 VBA Environment
03 File Types & Object Model
04 Object Model
Chapter 2
Your First Macro!
01 Record A Macro
02 Object Library & VBE
03 Special Cells
04 Absolute Vs Relative reference
05 Trigger Macro
06 Delete Comments
07 Reference Cells
Chapter 3
VBA Properties
01 Most Used Properties
02 Use of range proprties
03 Copy & resize Properties
04 Reference Worksheets
05 Reference Workbooks
Chapter 4
Range & variables in
VBA
01 Range_test
02 What is Variables
03 Object variables
04 Saving Workbook with Variables
05 With Block
Chapter 5
LOOPS & Conditions
01 For each Loop Collections
02 Simple IF
03 IF And OR Clause
04 Nested If Clause
05 VBA Functions & Exit Sub
Chapter 6
Advanced VBA
01 MsgBox
02 VBA Input Box
03 Excel Input Box
04 Debugging The Code
05 Error handling
06 Calling Procedures
Chapter 7
Advanced Loops
01 For Loop
02 For Nested Loop
03 Do While Loop
Chapter 8
Dir & Folders
01 Working with DIR & Folders
02 GetOpenFileName
03 FileDialog Loop
04 Workbook Events
05 Worksheet Event
Chapter 9
User Forms
01 Introduction to User Forms
02 Userform Videos
Chapter 10
Final Splitwise project
With VBA
01 Projetc Expense Sharing
02 Project Explaination
03 Project Solution
Chapter 1
Python Basics
01 Python variables
02 types Of Data
03 Anaconda AI
04 Basic Python Syntax
05 Operators
06 Conditional Statements
07 Defining a Function
Chapter 1
Python Basics Cont
08 Python Sequences [LIST]
09 Python Sequences [Using Methods]
10 Python Sequences [List Slicing]
11 Python Sequences [Dictionaries]
12 For Loop
13 Conditional Statements + Loops
14 Defining a Function With Dictionaries
Chapter 1
Python Coding
Fundamentals
01 Pbject Oriented Programming (OOP)
02 Modules, Packages, Python Standard Libraries
03 Importing Modules
04 Using Numpy & Pandas
05 Python Documentation
Chapter 1
Mathemetics for
Python
01 Matrix
02 Scalars and Vectors
03 Arrays in Python
04 Tensors
05 Transpose
06 Dot Product of Matrices
07 Why is Linear Algebra Useful
Chapter 1
Numpy For
Python
01 The NumPy Package and Why We Use It
02 Installing/Upgrading NumPy
03 Ndarray
04 The NumPy Documentation
05 NumPy Basics - Exercise
Chapter 1
Pandas For
Python
01 Introduction to pandas Series
02 Working with Attributes in Python
03 Using an Index in pandas
04 Using Methods in Python - Part I
05 Introduction to pandas DataFrames
Chapter 1
Working with Text Files
Python
01 Working with Files in Python - An Introduction
02 File vs File Object, Read vs Parse
03 Structured vs Semi-Structured & Unstructured Data
04 Data Connectivity through Text Files
05 Fixed-width Files
06 Importing Text Files in Python ( open() )
07 Importing multiple types of Files in Python
Chapter 1
Working with Text Data
Python
01 Working with Text Data and Argument Specifiers
02 Manipulating Python Strings
03 Using Various Python String Methods - Part I
04 Using Various Python String Methods - Part II
05 Using the .format() Method
Chapter 1
Must Know
Python Tools
01 Iterating Over Range Objects
02 Triple Nested For Loops
03 List Comprehensions
04 Anonymous (Lambda) Functions
05 What is data gathering/data collection?
Chapter 1
Data Cleaning & Pre
Prcocessing
01 Data Cleaning and Data Preprocessing
02 .unique(), .nunique()
03 .sort_values()
04 Attribute and Method Chaining
05 .sort_index()
Chapter 1
Pandas
Data Frames
01 Common Attributes for Working with DataFrames
02 Data Selection in pandas DataFrames
03 Data Selection - Indexing with .iloc[]
04 Data Selection - Indexing with .loc[]
05 A Few Comments on Using .loc[] and .iloc[]
Chapter 1
Numpy
Fundamentals
01 Indexing in NumPy
02 Assigning Values in NumPy
03 Elementwise Properties of Arrays
04 Types of Data Supported by NumPy
05 Characteristics of NumPy Functions
Chapter 1
Numpy
Data Types
01 ndarrays
02 Arrays vs Lists
03 Strings vs Object vs Number
04 NumPy DataTypes - Exercise
05 Numpy Vs Pandas
Chapter 1
Working With
Arrays
01 Basic Slicing in NumPy
02 Stepwise Slicing in NumPy
03 Conditional Slicing in NumPy
04 Dimensions and the Squeeze Function
05 Working with Arrays - Exercise
Chapter 1
Generating Data
With Numpy
01 Arrays of 0s and 1s
02 "_like" functions in NumPy
03 A Non-Random Sequence of Numbers
04 Random Generators and Seeds
05 Basic Random Functions in NumPy
06 Probability Distributions in NumPy
Chapter 1
Statistics
With Numpy
01 Using Statistical Functions in NumPy
02 Minimal and Maximal Values in NumPy
03 Statistical Order Functions in NumPy
04 Averages and Variance in NumPy
05 Histograms in NumPy
06 NAN Equivalent Functions in NumPy
Chapter 1
Numpy
Preprocessing
01 Checking for Missing Values in Ndarrays
02 Reshaping Ndarrays
03 Argument Where in NumPy
04 Striping Values from Ndarrays
05 Concatenating Ndarrays
06 Finding Unique Values in Ndarrays
Chapter 1
A Loan Data Excercise
Using Numpy
01 Introduction to the Practical Example
02 Splitting the Dataset
03 Creating Checkpoints
04 Manipulating Numeric Data: Currency Change
05 Manipulating Numeric Data: Substitute Filler
06 Completing the Dataset
Chapter 1
Data Visualization
Maplotlib
01 What Is Data Visualization and Why Is It Important?
02 Introduction into Colors and Color Theory
03 Bar Chart - Introduction - General Theory
04 Pie Chart – Interpreting the Pie Chart
05 Stacked Area Chart
06 Line Chart - Interpretation
Useful Data Analyst
Resources
1. Resources Every Aspiring Data Analyst
Needs
Books:The Data Warehouse Toolkit by Ralph Kimball,
Data Science for Business by Foster Provost
Online Courses: Coursera, Udacity, DataCamp,
theknowledgecastle
Communities: Kaggle, Reddit r/datascience, LinkedIn
groups, Stack Overflow
YouTube Channels: Knowledge Castle, StatQuest, Data
School, Ken Jee, alex the analyst
CONCLUSION
1. Some Final Words
Becoming a data analyst is an exciting journey filled with
opportunities. Continuous learning and hands-on experience
will set you apart in this competitive field.
2. Are You Ready to Take the Next Step?
Knowledge Castle offers industry-relevant training to help you
become a job-ready data analyst.
3. Know More About "The Complete Roadmap to
Data Analytics" - Enroll Now!
Join The Complete Roadmap to Data Analytics course and
master the skills needed to build a successful career in data
analytics. Enroll today and start your journey from learning to
earning!
CONTACT
INFORMATION
+91 8447754068
[email protected] www.theknowledgecastle.com
