Subcribe and Access : 5200+ FREE Videos and 21+ Subjects Like CRT, SoftSkills, JAVA, Hadoop, Microsoft .NET, Testing Tools etc..

Batch Date: Aug 5th @9:30PM

Faculty: Mrs. Sravanthi (9+ Yrs Of Exp,..)

Duration: 3.5 Months

Venue :
DURGA SOFTWARE SOLUTIONS,
Flat No : 202, 2nd Floor,
HUDA Maitrivanam,
Ameerpet, Hyderabad - 500038

Ph.No: +91- 8885252627, 9246212143, 80 96 96 96 96

Syllabus:

DATA ANALYST with GENERATIVE AI

COURSE:

• Advanced Excel
• MySQL
• POWER BI
• PYTHON
• Generative AI
• Git and GitHub

EXCEL

Introduction

• MS office Versions (similarities and differences)
• Interface (latest available version)
• Row and Columns
• Keyboard shortcuts for easy navigation
• Data Entry (Fill series)
• Find and Select
• Clear Options
• Ctrl + Enter
• Formatting Options - Font, Alignment
• Clipboard (copy, paste special))

Referencing, Named Ranges,Uses, Arithemetic Functions

• Mathematical calculations with Cell referencing (Absolute, Relative ,Mixed)
• Functions with Name Range
• Arithmetic functions (SUM,SUMIF,SUMIFS,COUNT,COUNTA,COUNTIFS,
  AVERAGE,AVERAGEIFS,MAX,MAXIFS,MIN,MINIFS)

Logical Functions

• IF
• AND
• OR
• NESTED IFS
• NOT
• IFERROR
• Usage of Mathematical and Logical functions nested together

Referring data from different tables: Various types of Lookup, Nested IF

• LOOKUP
• VLOOKUP
• NESTED VLOOKUP
• HLOOKUP
• INDEX
• INDEX WITH MATCH FUNCTION
• INDIRECT
• OFFSET

Advanced Functions

• Combination of Arithmatic, Logical, Lookup functions
• Data Validation (with Dependent drop down)

Date and Text Functions

• Date Functions: DATE,DAY,MONTH,YEAR,YEARFRAC,DATEDIFF,EOMONTH
• Text Functions:
  TEXT,UPPER,LOWER,PROPER,LEFT,RIGHT,SEARCH,FIND,MID,TTC, Flash Fill

Data Handling: Data cleaning, Data type identification, Remove Duplicates, Formatting and Filtering

• Number Formatting (with shortcuts)
• CTRL+T (Converting into an Excel Table)
• Formatting Table
• Remove Duplicate
• SORT
• Advanced Sort
• FILTER
• Advanced Filter

Data Visualization: Conditional Formatting, Charts

• Conditional formatting (icon sets/Highlighted colour sets/Data bars/custom formatting)
• Charts: Bar,Column,Lines,Scatter,Combo,Gantt,Waterfall,pie

Data Summarization: Pivot Report and Charts

• Pivot Reports:Insert,Interface,Crosstable Reports;Filter,Pivot Charts
• Slicers: Add,Connect to multiple reports and charts
• Calculated field
• Calculated item

Data Summarization: Dashboard Creation, Tips and Tricks

• Dashboard:Types,Getting reports and charts together, Use of Slicers.
• Design and placement: Formatting of Tables,Charts,Sheets,Proper use of Colours and Shapes

Connecting to Data: Power Query, Pivot, Power Pivot within Excel

• Power Query: Interface, Tabs
• Connecting to data from other excel files, text files, other sources
• Data Cleaning
• Transforming
• Loading Data into Excel Query
• Using Loaded queries
• Merge and Append
• Insert Power Pivot
• Similarities and Differences in Pivot and Power Pivot reporting
• Getting data from databases, workbooks, webpage

MySQL

Introduction to Mysql

• Introduction to Databases
• Introduction to RDBMS
• Explain RDBMS through normalization
• Different types of RDBMS
• Software Installation(MySQL Workbench)

SQL Commands and Data Types

• Types of SQL Commands (DDL,DML,DQL,DCL,TCL) and their applications
• Data Types in SQL (Numeric, Char, Datetime)

DQL & Operators

• SELECT, LIMIT, DISTINCT, WHERE, AND, OR, IN, NOT IN, BETWEEN, EXIST, ISNULL, IS NOT NULL, Wild Cards, ORDER BY

Case When Then and Handling NULL Values

• Usage of Case When then to solve logical problems and handling NULL Values (IFNULL, COALESCE)

Group Operations & Aggregate Functions

• Group By, Having Clause
• COUNT, SUM, AVG, MIN, MAX, COUNT
• String Functions
• Date & Time Function

Constraints

• NOT NULL
• UNIQUE
• CHECK
• DEFAULT
• Primary key
• Foreign Key (Both at column level and table level)

Joins

• Inner
• Left
• Right
• Cross
• Self Joins
• Full outer join

DDL

• Create
• Drop
• Alter
• Rename
• Truncate
• Modify
• Comment

DML & TCL Commands

• DML: Insert, Update & Delete
• TCL: Commit, Rollback, Savepoint & Data Partitioning

Indexes and Views

• Indexes (Different Type of Indexes) & Views in SQL

Stored Procedures

• Procedure with IN Parameter
• Procedure with OUT parameter
• Procedure with INOUT parameter

Function, Constructs

• User Define Function
• Window Functions: Rank, Dense Rank, Lead, Lag, Row_number

Union, Intersect, Sub-query

• Union
• Union all
• Intersect
• Sub Queries
• Multiple Query

Exception Handling

• Triggers

POWER BI

Power BI Introduction and Installation

• Understanding Power BI Background
• Installation of Power BI and check list for perfect installation
• Formatting and Setting prerequisits
• Understanding the difference between Power BI desktop & Power Query

The Power BI user interface, including types of data sources and visualizations

• Getting familiar with the interface BI Query & Desktop
• Understanding type of Visualisation
• Loading data from multiple sources
• Data type and the type of default chart on drag drop.
• Geo location Map integration

Sample dashboard with Animation Visual

• Finanical sample data in Power BI
• Preparing sample dashboard as get started
• Map visual Types and usages in different variation
• Understanding scatter Plot chart with Play axis and the parameters

Power BI Artificial Intelligence Visual

• Understanding the use of AI in power BI
• AI analysis in power bi using chart
• Q&A chat bot and the use in real life
• Hirarchy tree

Power BI Visualization

• Understanding Column Chart
• Understanding Line Chart
• Implementation of Conditional formating
• Implementation of Formating techniques

Power Query Editor

• Loading data from folder
• Understanding Power Query in detail
• Promote header, Split to limiter, Add columns, append, merge queries etc

Modelling with Power BI

• Loading multiple data from different format
• Understanding modelling (How to create relationship)
• Connection type, Data cardinality, Filter direction
• Making dashboard using new loaded data

Power Query Editor Filter Data

• Power Query Custom Column & Conditional Column
• Manage Parameter
• Introduction to Filter and types of filter
• Trend analysis, Future forecast

Customize the data in Power BI

• Understanding Tool tip with information
• Use and understanding of Drill Down
• Visual interaction and customisation of visual interaction
• Drill through function and usage
• Button triggers
• Bookmark and different use and implementation
• Navigation buttons

Dax Expressions

• Introduction to DAX
• Table Dax, Calculated column, DAX measure and difference
• Eg:- Calendar, Calendar auto, Summarize, Group by etc
• Calculated Column
• Related, Lookup value, switch, Datedif,Rankx,Date functions
• Dax Measure and Quick Measure
• Remove filters, Keep filters, All, Allselected, Time Intelligence Functions,
  Rolling average
• YoY, Running total

Data Analytical Expressions Functions

• IntroductiontoDAX functions
• New Measure,New Column and New Table
• Aggregate functions
  (AVERAGE, AVERAGEA, AVERAGEX, COUNT, COUNTBLANK, COUNTROWS, CO UNTX)
• DISTINCTCOUNT
• DISTINCTCOUNTNOBLANK
• MAX, MAXX, SUM, SUMX

INTRODUCTION TO DATE TIME FUNCTIONS

• CALANDER, HOUR, MINUTE, DATEDIFF, NETWORKDAYS, DAY, QUARTER, MONTH, SECOND
• YEAR, TIME, NOW, WEEKEND, TODAY, WEEKNUM, EOMONTH

Introduction to Text Functions

• COMBINE VALUES, CONCATENATE, CONCATENATEX, EXACT, SEARCH, FIND, FIXED, LEFT, RIGHT
• LEN, UPPER, MID, REPLACE, SUBSTITUTE, REPT, TRIM, VALUE, FORMAT

Introduction to Logical functions

• IF, AND(or)&&operator.OR(or)||operator,NOT (or) <> operator,SWITCH,COALESCE,TRUE

Introduction to information functions

• CONTAINS,CONTAINSROW,HASHONEFILTER,HASHONEVALUE, ISFILTERES, ISBLANK ISEMPTY, ISEVEN, USERNAME, USERPRINCIPLENAME

Introduction to filter functions

• CALCULATE, ALL,ALLCROSSFILTERED,ALLEXCEPT, ALLSELECTED, FILTERED, FIRST, LAST, LOOKUPVALUE, MAVINGAVERAGE, NEXT, PREVIOUS, RUNNINGSUM, SELECTEDVALUE

Time Intelligence functions

• OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR, CLOSINGBALANCEMONTH, CLOSINGBALANCEYEAR, DATEADD, DATESBETWEEN, DATEINPERIOD, DATEMTD, DATESQTD, DATESYTD ENDOFMONTH, ENDOFYEAR, FIRSTDATE. , FIRSTNOBALNK, LASTDATE, LASTNOBLANK, NEXTDAY, NEXTQUARTER, NEXTYEAR.

Real time scenarios on DAX functions

• 1st real time scenario explaination (1 to 11 questions)

Explanation about modelling

• Introduction to Model, Providing relationship between the data sets, Edit relationship, Cardinality, Cross filter direction, Making a relationship active or Inactive, How many columns we can provide relationship at a time, Deleting the relationship, Manage relationship, Triangle relationship not suppoted in power pi and why ?, Hiding columns in model, How can we refresh the data in the model?, How to remove multiple columns at a time?, How to avoid many to many relationships? How we can do the operations if the relationships is inactive?, Userrelationship function example, Introduction to Refresh option in power bi model

Discussing other options in Home Tab

• New visual options in Home Tab, text box option in Home Tab, Linked reports,
• More visuals options in Home Tab, Publish option

Custom Visual

• Custom visual and understanding the use of custom
• Loading custom visual, Pinning visual
• Loading to template for future use
• Publishinhg Power Bi

Power BI Service

• Introduction to app.powerbi.com
• Schedule refresh
• Data flow and use power bi from online
• Download data as live in power point and more.

Anaconda Installation,Introduction to python,Data types,Opearators

• Variables,data types(integer,Boolean,Float,List,tuple,string),Opearators in python

Data types Contd,Slicing the data,Inbuilt functions in python

• Dictionaries,Sequence methods,Concatenate,Repetition,len,min,max functions,Index
• position,Addition and deletion of elements,Reverse,Sorting

Sets,Set Theory,Regular Expressions,Decision making statements

• Sets,re module(findall,search,split,match),if,elifGetting input from user,Identity Operators

Loops,Functions,Lambda functions,Modules

• For,While loops,Functions,Lambda functions,Math module,Calender module,Date & time module

Pandas,Numpy,Matplotlib,Seaborn

• Data frame creation using different methods,Using Pandas anlysis on Universities,Salary data sets,Visualization using Matplotlib and Seaborn,Numpy introduction

Generative AI for Data Analysts

Module 1: Generative AI Fundamentals

• Introduction to AI
• AI Revolution
• Artificial Intelligence Fundamentals
• Machine Learning vs Deep Learning vs Generative AI
• History of AI
• AI Applications Across Industries
• AI Career Opportunities
• What is Generative AI?
• How Generative AI Works
• Tokens
• Context Window
• Temperature
• Hallucinations
• AI Ethics & Responsible AI
• AI Limitations
• Large Language Models (LLMs)
• What are LLMs?
• How LLMs are Trained
• Transformer Architecture (High-Level)
• Choosing the Right Model

Module 2: AI Tools for Data Analysts

• ChatGPT
• Google Gemini
• Claude
• Perplexity
• AI Tools Ecosystem
• AI Coding Assistants
• Cursor AI

Module 3: Prompt Engineering for Data Analysis

• Introduction to Prompt Engineering
• Anatomy of an Effective Prompt
• Prompt Engineering Best Practices
• Structured Output Prompting
• Prompt Optimization
• Prompt Debugging
• Prompt Libraries
• Business Prompt Engineering

Module 4: Practical AI for Data Analysts

• AI for Excel Automation
• AI for SQL Query Writing
• AI for Python Coding
• AI for Power BI & DAX
• AI Dashboard Design
• AI Report Generation
• AI Resume Builder
• AI Mock Interviews
• AI Productivity Techniques

Git & GitHub for Data Analyst

• Introduction to Git & Version Control
• Installing Git & GitHub Setup
• Git Commands & Repository Management
• Creating and Cloning Repositories
• GitHub Interface & Workflow
• Branching & Merging Concepts
• Commit, Push & Pull Operations
• Managing Data Analysis Projects with Git
• Uploading Excel, SQL & Python Projects to GitHub
• Collaboration using GitHub
• README File Creation & Documentation
• GitHub for Portfolio Building
• Version Tracking for Data Analyst Projects
• Real-Time Project Workflow
• Best Practices for Project Management GitHub Resume & Interview Preparation