Data Analyst Essential Course

From the Scratch

Course Objectives

By the end of this course, students will be able to:

Understand the role and responsibilities of a Data Analyst
Work confidently with Excel datasets, clean raw data, and apply key formulas
Build PivotTables, PivotCharts, and interactive reports
Understand relational databases and write SQL queries — filtering, sorting, grouping, and joining data
Import and transform data in Power BI, build relationships, and write basic DAX measures
Create professional Power BI dashboards
Analyse business data, identify trends, and present findings
Complete a small end-to-end Data Analytics project

Course Syllabus

Day 1 — Introduction to Data Analytics. Covers what data analytics is, the role of a Data Analyst versus a Data Scientist or BI Analyst, types of data (numerical, text, date/time, categorical), structured vs. unstructured data, and the full analytics workflow (collect, clean, transform, analyse, visualise, report). Practically, students explore a sample business dataset, learn to identify rows, columns, and records, and spot missing or incorrect data — finishing with an exercise inspecting a Supermarket Sales dataset for data quality issues.

Day 2 — Excel Fundamentals for Data Analysis. Introduces workbooks, worksheets, ranges, Tables, sorting, filtering, Freeze Panes, and number/currency/date formatting, along with absolute vs. relative references and core functions (SUM, AVERAGE, MIN, MAX, COUNT, COUNTA). Students build a sales analysis worksheet and calculate total, average, highest, and lowest sales.

Day 3 — Essential Excel Formulas. Focuses on logical functions (IF, IFS, AND, OR, IFERROR) and conditional functions (SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS), applied to real examples like sales above target, sales by category/salesperson/region, and average sales by product. Students create a Sales Performance Report.

Day 4 — Excel Lookup & Data Preparation. Covers lookup functions (XLOOKUP, VLOOKUP, HLOOKUP, INDEX, MATCH), text functions (LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, PROPER, CONCAT, TEXTJOIN), and date functions (TODAY, YEAR, MONTH, DAY, DATE, DATEDIF). Students practice combining a Sales Table, Product Table, and Customer Table using lookup functions.

Day 5 — Excel Data Cleaning & Analysis. Covers identifying and removing duplicates, handling blanks, Text-to-Columns, Find & Replace, Data Validation, and Flash Fill, plus an introduction to Power Query (importing data, changing data types, removing duplicates, replacing values, splitting/merging columns, and loading cleaned data). Students clean a messy customer/sales dataset into an analysis-ready table.

Day 6 — PivotTables & Excel Data Visualisation. Covers building PivotTables (rows, columns, values, filters, date grouping, sorting, calculated values) and PivotCharts (column, bar, line, pie/donut). Students create Monthly Sales, Sales by Product, Sales by Region, Salesperson Performance, and Category Performance reports, finishing with a mini task to build an Excel Sales Dashboard.

Day 7 — Introduction to SQL & Databases. Covers database fundamentals (tables, records, fields, primary/foreign keys, relational databases), an introduction to SQL syntax and keywords, and basic statements (SELECT, FROM, WHERE, ORDER BY). Students write basic queries against a sample sales database, such as SELECT * FROM Sales WHERE SalesAmount > 5000;

Day 8 — SQL Filtering, Sorting & Calculations. Covers comparison and logical operators (=, <>, >, <, AND, OR, NOT), filtering with IN, BETWEEN, LIKE, IS NULL/IS NOT NULL, and calculated columns using basic arithmetic. Students practice queries filtering by region, amount ranges, and name patterns.

Day 9 — SQL Aggregation & Grouping. Covers aggregate functions (COUNT, SUM, AVG, MIN, MAX) and grouping with GROUP BY and HAVING. Students answer business questions — total and average sales, top product, customer count, sales by region and by month — and build a SQL Sales Analysis Report.

Day 10 — SQL JOINs & Subqueries. Covers relational data across Customers, Products, Orders, and Employees tables, JOIN types (INNER, LEFT, RIGHT, and an introduction to FULL OUTER JOIN), and an introduction to subqueries. Students combine Customers, Orders, and Products tables to answer business questions using multiple related tables.

Day 11 — Power BI Introduction & Power Query. Introduces Power BI Desktop, Power BI Service, the Power BI ecosystem, and the Data Analyst workflow within it. Covers importing data from Excel, CSV, and SQL databases, then using Power Query to load, retype, filter, deduplicate, split, replace, and merge data. Students import and clean a sales dataset.

Day 12 — Power BI Data Modelling. Covers tables, relationships, primary/foreign keys, one-to-many relationships, fact and dimension tables, and star schema design. Students build a practical model with a Sales Fact Table related to Date, Product, Customer, Employee, and Region tables, along with creating/managing relationships, a date table, and basic model optimisation.

Day 13 — DAX Fundamentals. Covers what DAX is, the difference between calculated columns and measures, and essential functions (SUM, AVERAGE, COUNT, COUNTROWS, DISTINCTCOUNT, MIN, MAX, CALCULATE, FILTER, DIVIDE). Students create measures such as Total Sales, Total Quantity, Average Sales, and Total Customers, and build KPI calculations like Average Order Value and Achievement %.

Day 14 — Power BI Dashboard & Data Visualisation. Covers visualisation types (cards, bar/column/line/pie charts, tables, matrix, maps, slicers) and dashboard design principles — choosing the right chart, KPI cards, consistent layout, filters, drill-down, and tooltips. Students build a professional Sales Performance Dashboard with Total Sales, Total Orders, Total Customers, Average Order Value, Monthly Sales Trend, Sales by Region/Category, Top 10 Products, and Salesperson Performance.

Day 15 — Final Data Analyst Project. Students receive a raw retail/supermarket sales dataset and complete an end-to-end project: (1) Excel — inspect, clean, deduplicate, correct data types, and create calculated columns; (2) SQL — write queries for total/monthly sales, sales by region, top products/customers, average order value, and salesperson performance; (3) Power BI — import, transform, model relationships, build DAX measures, KPIs, charts, and an interactive dashboard; (4) Business Insights — identify at least 3 trends, 3 high-performing products/categories, 2 weak-performing areas, and 2 recommendations. The course closes with each student presenting their dashboard and answering: “What does the data tell us, and what should the business do?”

Core Topics Reference

Excel Formulas

Basic: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA
Logical: IF, IFS, AND, OR, IFERROR
Conditional: SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS
Lookup: XLOOKUP, VLOOKUP, INDEX, MATCH
Text: LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, PROPER, CONCAT, TEXTJOIN
Date: TODAY, DATE, DAY, MONTH, YEAR, DATEDIF

SQL Statements

Basic: SELECT, FROM, WHERE, DISTINCT, ORDER BY
Filtering: AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL
Aggregation: COUNT, SUM, AVG, MIN, MAX
Grouping: GROUP BY, HAVING
Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN
Additional: CASE, aliases, calculated fields, subqueries, basic date functions

Power BI

Power Query: import, clean, transform, merge, append, data types
Data Modelling: relationships, fact tables, dimension tables, star schema
DAX: measures, calculated columns, SUM, AVERAGE, COUNTROWS, DISTINCTCOUNT, CALCULATE, FILTER, DIVIDE
Visualisation: KPI cards, charts, tables, matrix, slicers, drill-down, interactive dashboards

Final Course Outcome

By the end of the 2 hours x 15-day course, students should have a practical, working data analytics flow:

Raw Data → Excel / Power Query → SQL → Power BI → Dashboard → Business Insights

Students should be able to take a simple business dataset, clean it, query it, analyse it, visualise it, and explain their findings.

Total Fees: 60,000/=
Total Duration: 30 Hrs (2 hours x 15 Classes)
Training Mode: Individual Training, your own timetable

Live Online Classes or Face to Face Direct Classes with our expert trainers.
Call +94 (0) 722000999 / +94 (0) 755123111 www.iss.lk. Medium : සිංහල / தமிழ் / English