1 Lakh+ Learners Trained
PAN India Training & Development
ISO 9001:2015 Certified
25+ Years of Training Experience
Home/ Courses/ Data Analytics

Data Analytics

#1 Institute in IT Training ยท 25+ Years of Experience

Master Data Analytics skills and take your career to the next level with Advanced MS Excel, Power BI, Power Query, DAX and MySQL training.

60 Hours3 Months Duration Offline ClassroomMode of Training Data AnalyticsAdvanced Excel ยท Power BI ยท MySQL Excel ยท Power BI ยท MySQLData Analysis & Reporting
โ˜…โ˜…โ˜…โ˜…โ˜…4.9/5Google Reviewsโ€ข1 Lakh+ learners
โฌ‡ Download Brochure

Master Data Analytics

The Data Analytics program is a 3-month, 60-hour offline training program covering Advanced MS Excel, Power BI, Power Query & M Language, DAX Expressions, Power BI Service & Data Modeling, and MySQL.

The supplied brochure highlights NIPSTec's 25+ years of experience, PAN India reach, trust by corporates, PSUs, government and private sector, qualified and experienced trainers, job-oriented programs, industry-relevant curriculum and ISO 9001:2015 certification.

Key Highlights

Advanced MS Excel

Cover advanced formatting, charts, date and time functions, filtering, validation, lookup, statistical, text and mathematical functions, Pivot Tables and security.

Power BI

Build reports and dashboards, work with visualizations, fields, filters, tables, hierarchies, drilldown, charts, maps and data models.

Power Query & M Language

Use Power Query Editor, query and dataset edits, imports, transformations, query groups, references, append and merge operations.

DAX Expressions

Learn DAX scope and context, operators, filter, aggregation and time intelligence functions, calculated columns and measures.

MySQL

Cover DBMS and RDBMS concepts, databases, tables, queries, clauses, conditions, keys, joins, indexes, users and privileges.

Data Reporting & Modeling

Publish reports to Power BI Service, share and collaborate, create relationships and build data models with DAX.

Course Overview

The program moves from advanced Excel fundamentals and analysis into Power BI reporting, Power Query transformations, DAX expressions, Power BI Service and data modeling, followed by MySQL database and query concepts.

60 HoursTraining Duration
3 MonthsProgram Duration
OfflineMode of Training
3 Core ToolsExcel ยท Power BI ยท MySQL
Advanced ExcelPower BIPower Query M LanguageDAXData ModelingMySQL

Program Curriculum

The curriculum below follows the supplied Data Analytics brochure, retaining its subject areas and terminology.

Module 1Advanced MS Excel โ€“ FundamentalsโŒ„
  • Introduction to Excel worksheet, Row, Columns, Cells etc.
  • Insert and delete worksheet, row and column.
  • Rename the sheet and delete multiple worksheets.
  • Customizing the Ribbon.
  • Currency format, Formatting Dates, Custom and special formats & Customizing Header & Footers.
  • Formatting cells with number formats, Font formats, Alignment, Borders, etc.
  • Basic and advance conditional formatting.
  • Printer Properties and Page Setup for Printing.
  • Insert the Logo to your worksheet while printing.
  • Various Chart i.e. Bar Charts / Column Charts / Pie Charts / Line Charts.
Module 2Excel Functions, Data Validation & AnalysisโŒ„
  • Date and Time Functions: Today, Now, Day, Month, Year, Date, Datedif, Edate, EOMonth.
  • Time, Text, hour, minute and second.
  • Weekday, workday, workday.INTL, networkDay, Networkdays.INTL.
  • Advance Filters, Sorting and Filtering.
  • Filtering on Text, Numbers & Colors.
  • Data Validation: Number, Date & Time Validation.
  • Text and List Validation.
  • Dynamic Dropdown List Creation using Data Validation.
  • Name Manager & What If Analysis.
  • Scenario Analysis & Data Tables.
  • Creating, Editing, and Deleting of Names.
  • Discussion on Name Ranges and Apply the Name Ranges on Cell and the combination of Cells.
Module 3Lookup, Statistical, Text & Mathematical FunctionsโŒ„
  • Lookup/Vlookup/Hlookup/Xlookup.
  • Index, Offset and Match function.
  • Row, Rows, Column, Columns.
  • Sort, unique.
  • Average, Averaga, Sum, Count, Counta, Max, Maxa, Min, Mina.
  • Countblank, Large, Small, Median, Mode, Stdev And Var.
  • Dsum, Dmax, Dmin, Daverage, Dcounta.
  • Pmt, Switch, Valuetotext, Yearfrac, Sequence, Sort And Filter.
  • Edit Custom List.
  • Consolidate data.
  • Conversion of Excel files to PDF/CSV/Notepad.
  • Removing Duplicates & Flash Fill.
  • Comments, Freeze Panes & Shortcut Keys.
  • Concatenate, Concate, Upper, Lower and Proper.
  • Len, Trim, Left, Right, Mid, Find and Replace.
  • Search, Substitute, Exact and Rept.
  • Sumif, Sumifs, Countif, Countifs and Averageif.
  • Averageifs, if, ifs, Abs, Sign and power.
  • not, Ifs, Iferror and Rank.
  • Round, Roundup, Rounddown and Mround.
Module 4Pivot Tables, Charts & Excel SecurityโŒ„
  • Creating Simple Pivot Tables.
  • Basic and Advanced value Field Setting.
  • Classic Pivot Table and Choosing Field.
  • Filtering Pivot Tables and Charts.
  • Using Slicer.
  • Worksheet Protection.
  • Workbook Protection.
  • Column Protection.
Module 5Introduction to Power BIโŒ„
  • Fundamentals of Power BI.
  • Power BI - Advantages and Scalable Options.
  • History - Power View, Power Query, Power Pivot.
  • Report Design with Database Tables.
  • Understanding Power BI Report Designer.
  • Report Canvas, Report Pages: Creation, Renames.
  • GET DATA Options and Report Fields, Filters.
  • Creating Power BI reports, auto filters.
  • Business Analyst Tools, MS Cloud Tools.
  • Power BI Installation and Cloud Account.
  • Power BI Cloud, service, architecture and Data Access.
  • Sample Reports and Visualization Controls.
Module 6Power BI Reports, Visualization & Data ModelingโŒ„
  • Report Design using Databases & Queries.
  • Building Home Page & Blog Section.
  • Stacked bar chart, Stacked column chart, Clustered bar chart, Clustered column chart.
  • Power BI Design: Canvas, Visualizations and Fields.
  • Import Data Options with Power BI Model, Advantages.
  • Report visualizations and properties.
  • Creating Customised Tables with Power BI Editor.
  • Alternate Text and Tiles. Header (Column, Row) Properties.
  • Table Styles & Alternate Row Colours - Static, Dynamic.
  • Hierarchies and Drilldown reports.
  • Hierarchy Levels and Drill Modes - Usage.
  • Drill-thru Options with Tree Map and Pie Chart.
  • Chart and map Report properties.
  • Line charts, area charts, stacked area charts.
  • Line and stacked row charts, line and stacked column charts.
  • Waterfall chart, scatter chart, pie chart.
  • Field Properties: Axis, Legend, Value, Tooltip, Colour Saturation, Filters Types.
  • Higher Levels and Next Level Navigation Options.
  • Multi Field Aggregations and Hierarchies in Power BI.
Module 7Power Query & M LanguageโŒ„
  • Understanding Power Query Editor - Options.
  • Power BI Interface and Query / Dataset Edits.
  • Working with Empty Tables and Load / Edits.
  • Sparse, Flashy Rows, Condensed Table Reports. Focus Mode.
  • Column Headers, Column Formatting, Value Properties.
  • Data Labels: Visibility, Colour and Display Units, Precision, Position, Text Options.
  • Toggle Options with Tabular Data. Filters.
  • Drilldown Buttons and Mouse Hover Options @ Visuals.
  • Data Imports and Query Marking in Query Editor.
  • REPLACE, REMOVE ROWS, REMOVE COL, BLANK - M Lang.
  • Column Splits and FilledUp / FilledDown Options.
  • Creating Query Groups and Query References.
  • Invoke Function and Freezing Columns.
  • Creating Reference Tables and Queries.
  • Detection and Removal of Query Datasets.
  • Blank Queries and Enumeration Value Generation.
  • Append data in different data source.
  • Merge data from multiple excel file/ or difference data source.
Module 8DAX Expressions & Power BI ServiceโŒ„
  • DAX EXPRESSIONS - Level 1.
  • Scope of Usage with DAX. Usability Options.
  • DAX Context: Row Context and Filter Context.
  • Invoke Function and Freezing Columns.
  • Creating Reference Tables and Queries.
  • Parenthesis, Comparison, Arithmetic, Text, Logic.
  • Filter, Aggregation and Time Intelligence Functions.
  • Syntax Requirements with DAX. Differences with Excel.
  • Creating reports and dashboard.
  • Publishing reports on Power BI Service.
  • Using Power BI Service for operations on reports.
  • Publishing reports to Power BI Service for sharing and collaboration.
  • Creating relationships between tables.
  • Building data models with calculated columns and measures using DAX (Data Analysis Expressions).
Module 9MySQL Overview, Database, Tables & QueriesโŒ„
  • DBMS & RDBMS Concepts.
  • MySQL History & Features.
  • MySQL Data Types & Connection.
  • Numeric: INT, DECIMAL, FLOAT, DOUBLE.
  • String: CHAR, VARCHAR, TEXT.
  • Date/Time: DATE, TIME, DATETIME, TIMESTAMP.
  • Boolean.
  • ENUM.
  • MySQL Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT, CHECK, AUTO_INCREMENT.
  • Query Rename, Load Enable and Data Refresh My SQL Options.
  • Create Database, Select Database, Drop Database, Show Database.
  • CREATE, ALTER & Show Table.
  • Rename, Describe & TRUNCATE Table.
  • DROP, Temporary & Copy Table.
  • Add/Delete, Show & Rename Column.
  • MySQL Queries.
  • INSERT Record.
  • UPDATE Record.
  • DELETE Record.
  • SELECT Record.
Module 10MySQL Clauses, Conditions, Joins & User ManagementโŒ„
  • MySQL WHERE.
  • MySQL DISTINCT.
  • MySQL FROM.
  • MySQL ORDER BY.
  • MySQL GROUP BY & HAVING.
  • MySQL AND & OR.
  • MySQL AND OR & LIKE.
  • MySQL IN & NOT.
  • MySQL IS NULL & IS NOT NULL.
  • MySQL BETWEEN.
  • Primary, Unique, Foreign & Default key.
  • MySQL JOIN/INNER JOIN.
  • MYSQL LEFT JOIN & RIGHT JOIN.
  • MYSQL CROSS JOIN & SELF JOIN.
  • MYSQL NATURAL JOIN.
  • Create, Show, Unique & Drop index.
  • MYSQL Create User.
  • MYSQL Drop User.
  • MYSQL Show Users.
  • Change User Password.
  • MYSQL Grant Privilege & Revoke Privilege.
  • MYSQL IF() & IFNULL().
  • MYSQL NULLIF() & CASE.
  • MySQL count() & sum().
  • MySQL avg(), min() & max().
  • MySQL String Functions: CONCAT(), UPPER(), LOWER(), LENGTH(), SUBSTRING(), REPLACE(), TRIM().
  • MySQL Numeric Functions: ROUND(), CEIL(), FLOOR(), ABS(), MOD(), POWER().
  • MySQL Date Functions: NOW(), CURDATE(), CURTIME(), YEAR(), MONTH(), DAY(), DATEDIFF(), DATE_FORMAT().
  • MySQL Views: What is a View?
  • CREATE VIEW.
  • ALTER VIEW.
  • DROP VIEW.
  • Updatable views.
  • Advantages of views.

Tools & Technologies You'll Learn

The brochure's visual Tools to Master section specifically highlights the following core tools.

Microsoft ExcelPower BIMySQL Power QueryM LanguageDAX

Career-Ready Data Analytics Skills

The program develops practical skills for spreadsheet analysis, business reporting, data transformation, modeling and database querying.

Advanced Excel Analysis

Work with advanced formulas, lookup and reference functions, Pivot Tables, charts, validation, filtering and What If Analysis.

Power BI Reporting

Create reports and dashboards with visualizations, filters, tables, hierarchies, drilldown, charts and maps.

Data Transformation

Use Power Query and M Language for imports, edits, transformations, append and merge operations and query references.

DAX & Data Modeling

Apply row and filter context, filter and aggregation functions, relationships, calculated columns and measures.

MySQL Database Skills

Create and manage databases and tables and work with INSERT, UPDATE, DELETE and SELECT queries.

SQL Joins & Administration

Use clauses, conditions, keys, joins, indexes, users, privileges and control-flow functions in MySQL.

Learning & Practical Support

Structured Learning

The curriculum progresses from Advanced Excel through Power BI, Power Query, DAX, data modeling and MySQL.

Offline Training

The brochure lists Offline as the mode of training for the 3-month, 60-hour program.

Reporting & Visualization

Build Power BI reports, dashboards and visualizations and work with fields, filters, hierarchies, drilldown and charts.

Data Preparation

Work with Power Query Editor, data imports, query edits, transformations, append, merge and reference operations.

Excel & DAX Practice

Develop advanced Excel function skills and use DAX for calculations, filtering, aggregation and time intelligence.

Database Practice

Learn MySQL database, table, query, clause, condition, join, index, user and privilege concepts.

Why Choose NIPSTec

The brochure highlights 25+ years of excellence in training students and corporates, PAN India reach, trust by corporates, PSUs, government and private sector, qualified and experienced trainers, job-oriented programs and industry-relevant curriculum. It also identifies NIPSTec as an ISO 9001:2015 certified company.

25+ Years of Experience

Experience in training students and corporates.

Qualified & Experienced Trainers

Qualified and experienced trainers are highlighted by the brochure.

Industry-Relevant Curriculum

The brochure highlights industry-relevant curriculum and job-oriented programs.

Job-Oriented Programs

NIPSTec identifies its programs as job-oriented.

PAN India Reach

The brochure highlights PAN India reach and trust across multiple sectors.

ISO 9001:2015 Certified

The brochure identifies NIPSTec as an ISO 9001:2015 certified company.

Enquire About Data Analytics

Submit your details to get course information, batch guidance and admission assistance.

Interested in Data Analytics?

Share your details โ€” our counsellor will call you within 24 hours.

Data Analytics FAQs

Common questions about the program

What is the duration of the Data Analytics program?๏ผ‹

The supplied brochure lists 3 Months and 60 Hours Duration.

Is the Data Analytics training offline?๏ผ‹

Yes. The brochure lists Offline as the mode of training.

Which tools are covered in the course?๏ผ‹

The brochure's Tools to Master section highlights Excel, Power BI and MySQL. The curriculum also covers Power Query, M Language and DAX.

What Advanced Excel topics are included?๏ผ‹

The curriculum covers Excel fundamentals, formatting and proofing, charts, date and time functions, sorting and filtering, data validation, What If Analysis, lookup and reference functions, statistical and other functions, import and export, text and mathematical functions, Pivot Tables, charts and Excel security.

Does the course cover Power BI reports and dashboards?๏ผ‹

Yes. It includes Power BI reports, auto filters, report design, visualizations and properties, hierarchies and drilldown reports, charts and maps, Power BI Service and dashboard/report publishing.

Does the program include Power Query and M Language?๏ผ‹

Yes. Power Query & M Language Parts 1 and 2 include Query Editor, data and query edits, imports, transformations, append and merge, query groups, references and other listed operations.

What is covered in DAX?๏ผ‹

The brochure includes DAX Expressions Level 1, DAX scope and usability, row and filter context, operators, filter, aggregation and time intelligence functions, and data modeling with calculated columns and measures.

What MySQL topics are included?๏ผ‹

The curriculum covers DBMS/RDBMS concepts, MySQL databases, tables, views, queries, clauses, conditions, keys, joins, indexes, user management, privileges and control-flow functions.

Where is the NIPSTec training location shown on the brochure?๏ผ‹

The brochure's contact information lists D-82, First Floor, Malviya Nagar, New Delhi - 110017 (India).

โฌ‡ Download Brochure