Data Analyst

Duration-120

MS-EXCEL Training Module

Advanced Excel

1. Pivots and Pivot Graphs:

a) Creating of Pivot Table
b) Using Summary function in Pivot Table
c) Pivot Table Calculations
d) Creating own formulas in Pivots
e) Grouping and Ungrouping in Pivot Tables
f) Creating Pivot Graph
g) Custom Filter on Pivot Table
h) Formatting Pivot table (Removing old data, grand total, subtotal
…etc.)

2. Working on Objects:
a) Inserting Objects
b) Inserting different files into Excel (like .doc, .pdf, .txt files)
c) Hyper linking to different sheets
d) Providing screen tips in hyper link
e) Hyperlink to send emails
f) Inserting symbols
g) Sending working file to an email

3. Data Management in Excel:

Sorting:

a. Rearranging of Data
b. Sorting by alphabets, numbers and time
Filtering Data:
a. Using Auto filter option
b. Custom Filtering with different options
c. Advanced Filtering
d. Find Unique records using advanced filtering
e. Filter using operators AND/OR
f. Filter by color
Grouping and Outlining Data
a. Group and ungroup rows and columns
b. Auto outlining the data
Freezing and Unfreezing
Data Validation:
a. Use Data Validation in Excel
b. Using Input Message in Data validation
c. Using error message in Data Validation
Removing duplicate records:
a. Remove duplicate records in a spread sheet
b. Customize to remove the duplicate values
Split Texts:
a. Split texts to different columns
b. Remove special character from a string and split the
string
Data Consolidation:
a. Combining different spreadsheet using data consolidation
b. Using summary function in data consolidation
Conditional Formatting:
a. Use of conditional formatting
b. Custom formatting
Copy Data:
a. Use of format painter
b. Use of paste special
c. Strike through a value
d. Find and Replace
View Multiple Files:
a. Arrange multiple windows to view in one time.
b. Split big sheet into sub sheets for better analysis
Working on Tab:
a. Hide/ Unhide a tab
b. Color a tab
 c. Move /Copy the tab to another sheet/workbook

4. Advanced Functions:
a. V-Lookup
b. H-Lookup
c. IF
d. ISERROR
e. Index Match
f. Rows
g. Columns
h. Match
i. Offset
j. Get Pivot Data
k. Date and Time Functions
l. Text Functions
m. Operators
n. Mathematical functions
o. Calculation options

Name Manager:
a. Define a name to a range
b. Use of name manager
c. Using formulas in name manager
d. Editing name range

5. Data Connection from External Data:

a. From access
b. From SQL
c. From Web
d. From ODBC

6. Customize your Excel view:

a. Customize Excel view
b. Developer Option
c. Trust Centre
d. Add-Ins
e. Save directory options
f. Language Setting
g. Quick access toolbar
7. Security options in excel:
a. Protecting the worksheet
b. Protecting the workbooks
c. Sharing the workbook
d. Allow users to edit range

Microsoft Excel VBA

1. Introduction to VBA
o WHAT IS VBA?
o INTRODUCTION TO ANOTHER LANGUAGES
o VBA: AN EVENT DRIVEN PROGRAMMING
o VBA: AN OBJECT BASED PROGRAMMING
o ADVANTAGES AND DISADVANTAGES OF VBA
o OBJECTS, PROCEDURES AND PROPERTIES

2. VBA IDE
o OPENING THE EXCEL VBA IDE
o MENU BAR
o TOOLBAR
o PROJECT WINDOW
o CODE WINDOW
o IMMEDIATE WINDOW
o CUSTOMIZING THE VBE

3. Getting Started With Macro

o RECORDING THE MACRO
o EXAMINING THE MACRO
o SAVING WORKBOOKS THAT CONTAIN MACROS

4. Fundamentals of VBA Language

o VARIABLE & CONSTANTS
o KEYWORDS
o DATA TYPES
o PROCEDURES: SUB AND FUNCTION
o ARGUMENTS
 o LOCAL VS GLOBAL VARIABLE DECLARATION
o PROCEDURES: PUBLIC OR PRIVATE
o COMMENTS

5. Control Flow & Loops Statements

o RELATIONAL AND LOGICAL OPERATORS
o IF....THEN
o IF....THEN....ELSE
o IF....THEN....ELSEIF....ELSE
o SELECT CASE ( WITH TO AND WITH IS)
o DO WHILE....LOOP
o DOLOOP....WHILE
o DO UNTIL....LOOP
o DOLOOP....UNTIL
o FOR....NEXT
o FOR EACH....NEXT
o WHILE....WEND
o THE EXIT STATEMENT

6. Using VBA and Worksheet Functions

o VB FUNCTIONS
o EXCEL FUNCTIONS
o THE WORKBOOKS COLLECTION
o THE SHEETS COLLECTION
o ACTIVATE AND SELECT
o RANGE PROPERTY
o CELLS PROPERTY
o OFFSET PROPERTY
o RESIZE PROPERTY
o CURRENTREGION PROPERTY
o COLUMNS AND ROWS PROPERTIES
o REFERENCING RANGES IN OTHER SHEETS
o USING THE UNION AND INTERSECT METHODS
o USING THE ISEMPTY FUNCTION
o USING THE AREAS COLLECTION
o TRANSFERRING VALUES BETWEEN ARRAYS AND RANGES

8. Working with Dialog Boxes and Form Objects

o USING LABELS
o USING TEXT BOXES
o USING THE COMMANDBUTTON CONTROL
o USING LIST BOXES
o USING COMBO BOXES
 o USING CHECKBOXES
o USING RADIO BUTTONS
o USING TOGGLE BUTTONS
o USING FRAMES
o USING MESSAGE BOXES
o TAKING INPUT USING INPUT BOXES
o TABSTRIP
o USING A SCROLLBAR AS A SLIDER TO SELECT VALUES
o ADDING HELP TIPS TO CONTROLS
o TAB ORDER
o COLOURING THE ACTIVE CONTROL
o TRANSPARENT FORMS
o WORKING WITH MENUS AND TOOLBARS
o CREATING MENUS AND TOOLBARS

9. Handling Errors
o TYPES OF ERRORS IN VBA
o USING THE ON ERROR GOTO STATEMENT
o USING THE RESUME STATEMENT
o WORKING WITH THE ERR OBJECT

7. The Excel Object Model

o INTRODUCING CLASSES AND OBJECTS
o THE OBJECT HIERARCHY
o COLLECTIONS
o REFERRING TO OBJECTS
o NAVIGATING THROUGH THE HIERARCHY
o OBJECT PROPERTIES AND METHODS
o EVENTS
o USING THE WITH…END WITH STATEMENT

10. Arrays
o DECLARING ARRAYS
o SPECIFYING LIMITS
o ARRAY FUNCTION
o TWO DIMENSIONAL ARRAYS
o MULTI DIMENSIONAL ARRAYS
o DYNAMIC ARRAYS
 11. The Application Object
o GLOBALS
o THE ACTIVE PROPERTIES
o DISPLAY ALERTS
o SCREEN UPDATING
o EVALUATE
o STATUSBAR
o SEND KEYS
o ONTIME & ON KEY

12. Data Lists

o SORTING A RANGE
o CREATING A TABLE
o SORTING A TABLE
o AUTOFILTER

SQL – Part 1
1. Users and Schemas
1. Introduction to Users and Schemas
1. Definitions and Client Tools
2. SQL Developer Login
2. Creating An Application Schema
2. Pseudo Columns & Functions
1. Using The Dual Table
2. Functions
3. Pseudo-Columns
4. Using Rowid
5. Using Rownum
3. Using Subqueries
1. Simple Subqueries
2. Inline Views
3. Correlated Subqueries
4. Scalar Subquery
4. Joining Tables
1. Review of Joins
2. Equijoins
1. Inner Joins
 2. Outer Joins
3. Cross Joins
4. Natural Joins
5. Using Named Subqueries
5. Group Operations
1. About Group Processing
2. Simple GROUP BY
3. ROLLUP Function
6. Using Set Operators
1. Set Operators Defined
2. Relationship to Mathematical Set Theory
3. Restrictions on Set Operators
7. Conditional Processing
1. The DECODE Function
2. The Case Expression
3. Exercise: Conditional Processing
8. SQL Functions (Character)
1. What Are SQL Functions?
2. Character Functions
1. CONCAT
2. LENGTH
3. INSTR
4. REPLACE
5. UPPER
6. LOWER
7. INITICAP
8. LPAD
9. RPAD
10. TRIM
11. TO_CHAR
12. SOUNDEX
9. SQL Functions (Non-Character)
1. Numeric Functions
1. The TO_CHAR Function with numbers
2. Date/Time Functions
3. Date Format Functions
4. Date Arithmetic Functions
5. Null Value Functions
10. SQL Data Manipulation Language
1. The INSERT Statement
 1. INSERT Statement Categories
2. The UPDATE Statement
3. The DELETE Statement
4. Transactions
5. Complex Table References
6. The MERGE statement

SQL – Part 2 – Advanced:

11. Analytical Functions

12. Queries for Queries
13. Techniques to compare data.
14. Efficiently writing SQL queries.

POWER BI Course Content

INTRODUCTION TO POWER BI
 Introduction to Power BI - Need, Imprtance
 Power BI - Advantages and Scalable Options
 History - Power View, Power Query, Power Pivot
 Power BI Data Source Library and DW Files
 Cloud Colloboration and Usage Scope
 Business Analyst Tools, MS Cloud Tools
 Power BI Installation and Cloud Account
 Power BI Cloud and Power BI Service
 Power BI Architecture and Data Access
 OnPremise Data Acces and Microsoft On Drive
 Power BI Desktop - Instalation, Usage
 Sample Reports and Visualization Controls
 Power BI Cloud Account Configuration
 Understanding Desktop & Mobile Editions
 Report Rendering Options and End User Access
 Power View and Power Map. Power BI Licenses
 Course Plan - Power BI Online Training

CREATING POWER BI REPORTS, AUTO FILTERS

 Report Design with Legacy & .DAT Files
 Report Design with Databse Tables
 Understanding Power BI Report Designer
 Report Canvas, Report Pages: Creation, Renames
 Report Visuals, Fields and UI Options
 Experimenting Visual Interactions, Advantages
 Reports with Multiple Pages and Advantages
 Pages with Multiple Visualizations. Data Access
 PUBLISH Options and Report Verification in Cloud
 "GET DATA" Options and Report Fields, Filters
 Report View Options: Full, Fit Page, Width Scale
 Report Design using Databases & Queries
 Query Settings and Data Preloads
 Navigation Options and Report Refresh
 Stacked bar chart, Stacked column chart
 Clustered bar chart, Clustered column chart
 Adding Report Titles. Report Format Options
 Focus Mode, Explore and Export Settings

REPORT VISUALIZATIONS and PROPERTIES

 Power BI Design: Canvas, Visualizations and Fileds
 Import Data Options with Power BI Model, Advantages
 Direct Query Options and Real-time (LIVE) Data Access
 Data Fields and Filters with Visualizations
 Visualization Filters, Page Filters, Report Filters
 Conditional Filters and Clearing. Testing Sets
 Creating Customised Tables with Power BI Editor
 General Properties, Sizing, Dimensions, and Positions
 Alternate Text and Tiles. Header (Column, Row) Properties
 Grid Properties (Vertical, Horizontal) and Styles
 Table Styles & Alternate Row Colors - Static, Dynamic
 Sparse, Flashy Rows, Condensed Table Reports. Focus Mode
 Totals Computations, Background. Boders Properties
 Column Headers, Column Formatting, Value Properties
 Conditional Formatting Options - Color Scale
 Page Level Filters and Report Level Filters
 Visual-Level Filters and Format Options
 Report Fields, Formats and Analytics
 Page-Level Filters and Column Formatting, Filters
 Background Properties, Borders and Lock Aspect

CHART AND MAP REPORT PROPERTIES

 CHART Report Types and Properties
 STACKED BAR CHART, STACKED COLUMN CHART
 CLUSTERED BAR CHART, CLUSTERED COLUMN CHART
 100% STACKED BAR CHART, 100% STACKED COLUMN CHART
 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
 Field Properties: Color Saturation, Filters Types
 Formats: Legend, Axis, Data Labels, Plot Area
 Data Labels: Visibility, Color and Display Units
 Data Labels: Precision, Position, Text Options
 Analytics: Constant Line, Position, Labels
 Working with Waterfall Charts and Default Values
 Modifying Legends and Visual Filters - Options
 Map Reports: Working with Map Reports
 Hierarchies: Grouping Multiple Report Fields
 Hierarchy Levels and Usages in Visualizations
 Preordered Attribute Collection - Advantages
 Using Field Hierarchies with Chart Reports
 Advanced Query Mode @ Connection Settings - Options
 Direct Import and In-memory Loads, Advantages

HIERARCHIES and DRILLDOWN REPORTS

 Hierarchies and Drilldown Options
 Hierarchy Levels and Drill Modes - Usage
 Drill-thru Options with Tree Map and Pie Chart
 Higher Levels and Next Level Navigation Options
 Aggregates with Bottom/Up Navigations. Rules
 Multi Field Aggregations and Hierarchies in Power BI
 DRILLDOWN, SHOWNEXTLEVEL, EXPANDTONEXTLEVEL
 SEE DATA and SEE RECORDS Options. Differences
 Toggle Options with Tabular Data. Filters
 Drilldown Buttons and Mouse Hover Options @ Visuals
 Dependant Aggregations, Independant Aggregations
 Automated Records Selection with Tabular Data
 Report Parameters : Creation and Data Type
 Available Values and Default values. Member Values
 Parameters for Column Data and Table / Query Filters
 Parameters Creation - Query Mode, UI Option
 Linking Parameters to Query Columns - Options
 Edit Query Options and Parameter Manage Entries
 Connection Parameters and Dynamic Data Sources
 Synonyms - Creation and Usage Options

POWER QUERY & M LANGUAGE - Part 1

 Understanding Power Query Editor - Options
 Power BI Interface and Query / Dataset Edits
 Working with Empty Tables and Load / Edits
 Empty Table Names and Header Row Promotions
 Undo Headers Options. Blank Columns Detection
 Data Imports and Query Marking in Query Editor
 JSON Files & Binary Formats with Power Query
 JavaScript Object Notation - Usage with M Lang.
 Applied Steps and Usage Options. Revert Options
 creating Query Groups and Query References. Usage
 Query Rename, Load Enable and Data Refresh Options
 Combine Queries - Merge Join and Anti-Join Options
 Combine Queries - Union and Union All as New Dataset
 M Language : NestedJoin and JoinKind Functions
 REPLACE, REMOVE ROWS, REMOVE COL, BLANK - M Lang
 Column Splits and FilledUp / FilledDown Options
 Query Hide and Change Type Options. Code Generation

POWER QUERY & M LANGUAGE - Part 2

 Invoke Function and Freezing Columns
 Creating Reference Tables and Queries
 Detection and Removal of Query Datasets
 Custom Columns with Power Query
 Power Query Expressions and Usage
 Blank Queries and Enumuration Value Generation
 M Language Sematics and Syntax. Tranform Types
 IF..ELSE Conditions, TransformColumn() Types
 RemoveColumns(), SplitColumns(),ReplaceValue()
 Table.Distinct Options and GROUP BY Options
 Table.Group(), Table.Sort() with Type Conversions
 PIVOT Operation and Table.Pivot(). List Functions
 Using Parameters with M Language (Power Query Editor)
 Advanced Query Editor and Parameter Scripts
 List Generation and Table Conversion Options
 Aggregations using PowerQuery & Usage in Reports
 Report Generation using Web Pages & HTML Tables
 Reports from Page collection with Power Query
 Aggregate and Evaluate Options with M Language
 Creating high-density reports, ArcGIS Maps, ESRI Files
 Generating QR Codes for Reports
 Table Bars and Drill Thru Filters

DAX EXPRESSIONS - Level 1

 Purpose of Data Analysis Expresssions (DAX)
 Scope of Usage with DAX. Usabilty Options
 DAX Context : Row Context and Filter Context
 DAX Entities : Calculated Columns and Measures
 DAX Data Types : Numeric, Boolean, Variant, Currency
 Datetime Data Tye with DAX. Comparison with Excel
 DAX Operators & Symbols. Usage. Operator Priority
 Parenthesis, Comparison, Arthmetic, Text, Logic
 DAX Functions and Types: Table Valued Functions
 Filter, Aggregation and Time Intelligence Functions
 Information Functions, Logical, Parent-Child Functions
 Statistical and Text Functions. Formulas and Queries
 Syntax Requirements with DAX. Differences with Excel
 Naming Conventions and DAX Format Representation
 Working with Special Characters in Table Names
 Attribute / Column Scope with DAX - Examples
 Measure / Column Scope with DAX - Examples

DAX EXPRESSIONS - Level 2

 YTD, QTD, MTD Calculations with DAX
 DAX Calculations and Measures
 Using TOPN, RANKX, RANK.EQ
 Computations using STDEV & VAR
 SAMPLE Function, COUNTALL, ISERROR
 ISTEXT, DATEFORMAT, TIMEFORMAT
 Time Intelligence Functions with DAX
 Data Analysis Expressions and Functions
 DATESYTD, DATESQTD, DATESMTD
 ENDOFYEAR, ENDOFQUARTER,ENDOFMONTH
 FIRSTDATE, LASTDATE, DATESBETWEEN
 CLOSINGBALANCEYEAR,CLOSINGBALANCEQTR
 SAMEPERIOD and PREVIOUSMONTH,QUARTER
 KPIs with DAX. Vertipaq Queries in DAX
 IF..ELSEIF.. Conditions with DAX
 Slicing and Dicing Options with Columns, Measures
 DAX for Query Extraction, Data Mashup Operations
 Calcualted COlumns and Calculated Measures with DAX

POWERBI DEPLOYMENT & CLOUD

 PowerBI Report Validation and Publish
 Understanding PowerBI Cloud Architecture
 PowerBI Cloud Account and Workspace
 Reports and DataSet Items Validation
 Dashboards and Pins - Real-time Usage
 Dynamic Data Sources and Encryptions
 Personal and Organizational Content Packs
 Gateways, Subscriptions, Mobile Reports
 Data Refresh with Power BI Architecture
 PBIX and PBIT Files with Power BI - Usage
 Visual Data Imprts and Visual Schemas
 Cloud and On-Premise Data Sources
 How PowerBI Supports Data Model?
 Relation between Dashbaords to Reports
 Relation between Datasets to Reports
 Relation between Datasets to Dashbaords
 Page to Report - Mapping Options
 Publish Options and Data Import Options
 Need for PINS @ Visuals and PINS @ Reports
 Need for Data Streams and Cloud Intergration

POWER BI CLOUD OPERATIONS

 Report Publish Options and Verifications
 Working with Power BI Cloud Interface & Options
 Navigation Paths with "My Workspace" Screens
 FILE, VIEW, EDIT REPORTS, ACCESS, DRILLDOWN
 Saving Reports into pdf, pptx, etc. Report Embed
 Report Rendering and EDIT, SAVE, Print Options
 Report PIN and individual Visual PIN Options
 Create and Use Dashboards. Menu Options
 Goto Dashboard and Goto LIVE Page Options
 Operations on Pinned Reports and Visuals
 TITLE, MEDIA, USAGE METRICS & FAVOURITES
 SUBSCRIPTION Options and Reports with Mobile View
 Options with Report Page : Print and Subscribe
 Report Actions: USAGE METRICS, ANALYSE IN EXCEL
 Report Actions: RELATED ITEMS, RENAME, DELETE
 Dashboard Actions: METRICS, RELATED ITEMS
 Dashboard Actions: SETTINGS FOR Q & A, DELETE
 PIN Actions: METRICS, SHARE, RELATED ITEMS
 PIN Actions: SETTINGS FOR Q & A, DELETE
 EDIT DASHBOARD (CLOUD), On-The-Fly Reports
 Dataset Actions: CREATE REPORT, REFRESH
 SCHEDULED REFRESH & RELATED ITEMS
 Dashboard Integration with Apps in Power BI

IMPROVING POWER BI REPORTS

 Publish PowerBI Report Templates
 Import and Export Options with Power BI
 Dataset Navigations and Report Navigations
 Quick Navigation Options with "My Workspace"
 Dashboards, Workbooks, Reports, Datasets
 Working with MY WORK SPACE group
 Installing the Power BI Personal Gateway
 Automatic Refresh - Possible Issues
 Adding images to the dashboards
 Reading & Editing Power BI Views
 Power BI Templates (pbit)- Creation, Usage
 Managing report in Power BI Services
 PowerBI Gateway - Download and Installation
 Personal and Enterprise Gateway Features
 PowerBI Settings : Dataset - Gateway Integration
 Configuring Dataset for Manual Refresh of Data
 Configuring Automatic Refresh and Schdules
 Workbooks and Alerts with Power BI
 Dataset Actions and Refresh Settings with Gateway
 Using natural Language Q&A to data - Cortana

INSIGHTS AND SUBSCRIPTIONS

 Data Navigation Paths and Data Splits
 Getting data from existing systems
 Data Refresh and LIVE Connections
 pbit and pbix : differences. Usage Options
 Quick Insights For Power BI Reports
 Quick Insights For PowerBI Dashboads
 Generating Insights with Cloud Datasets
 Generarting Reports with Cloud Datasets
 Using relational databases on-premises
 Using relational databases in the cloud
 Consuming a service content pack
 Creating a custom data set from a service
 Creating a content pack for your organization
 Consuming an organizational content pack
 Updating an organizational content pack
 Adding Tiles : Images, Videos, DataStreams
 Creating New Reports from Cortana, Advantages

POWERBI INTEGRATION ELEMENTS

 SSRS Integration with Power BI
 SSRS Report Portal URL to Power BI Cloud
 Power BI KPI Reports Vs SSRS KPI Reports
 Convering and Working with Mobile Reports
 Report Buidler Reports to Powert BI
 Generating QR Codes and Report Security
 Reporting JSON Files, Bulk Data Loads
 Creating high-density Reports in Power BI
 OLAP DataSources in Power BI
 Using MDX Queries with PowerBI Queries
 MDX SELECT and Perspective Access
 KPIs and MDX Expressions with Power BI
 MDX Queries and Filters with Power BI
 Linked Servers and T-SQL SPROCs with MDX
 YTD, PARALLELPERIOD,SCOPE, ALLMEMBERS
 WHERE, EXCEPT, RANGE, NONEMPTY
 CURRENT & EMPTY, AND / OR, LEFT / RIGHT
 Implementing Row Level Security (RLS)
 Security Roles and Role Members. Tests
 Using R for Power BI, Streaming DataSets
 Azure Connections with PowerBI Desktop
 PowerBI Reports using SQL Azure DBs

SECTION 1: PYTHON
Module 1. Introduction
 Python - Variables and data types
 Python - Data Structures in Python ● Python - Functions and
methods
 Python - If statements
 Python - Loops
 Python - Python syntax essentials
 Python - Writing/Reading/Appending to a file ● Python - Common
pythonic errors
 Python - Getting user Input
 Python - Stats with python
 Python - Module Import
 Python – List, Multidimensional lists and Tuples ● Python - Reading
from CSV
 Python - Multi Line Print
  List Comprehension
 Python - Dictionaries
 Python - Built in functions
 Error handling
 OS module
 Python memory utilization

Module 2. Jupyter and Numpy

 Python Numpy - Introduction

 Python Numpy - Creating an Array
 Python Numpy - Reading Text Files
 Python Numpy - Array Indexing
 Python Numpy - N-Dimensional Arrays ● Python Numpy - Data Types
 Python Numpy - Array Math
 Python Numpy - Array Methods
 Python Numpy - Array Comparison and Filtering ● Python Numpy -
Reshaping and Combining Arrays

Module 3. Pandas and Matplotlib

 Python Pandas – Introduction

 Introduction to Data Structures
 Python Pandas – Series
 Python Pandas – DataFrame
 Python Pandas – Basic Functionality ●Python Pandas – Descriptive
Statistics ●Python Pandas – Indexing and Selecting Data ●Python
Pandas – Function Application ●Python Pandas – Reindexing
 Python Pandas – Iteration
 Python Pandas – Sorting
 Python Pandas – Working with Text Data ●Python Pandas – Options
and Customization ●Python Pandas – Missing Data
 Python Pandas – GroupBy
 Python Pandas – Merging/Joining
 Python Pandas – Concatenation
 Python Pandas – IO Tools
 Python Pandas – Dates Conversion
 One industry case study analysis as EDA (exploratory data analytics) in
pandas
