FEATURES AND FUNCTIONALITY PART 1

• Introduction of Excel compare MS Excel 2016 with other Excel versions
• Copy/Paste, Paste Special
• Add and Edit Comments
• Fill Effects
• Wrapping text within a cell
• Data Formatting
• Filter, Advanced Filter, Color Filter
• Sorting, Advance Sorting, Custom Sorting
• Flash Fill Feature
• Text-to-columnmn functionality of excel to Split words
• Absolute, Relative, and Mixed Cell References
• Protect sheet and workbook
• Protect Range
• Table Feature
• Freeze and unfreeze panes and Split
• Excel Keyboard Shortcut Keys
• Conditional and Custom Formatting

BASIC AND INTERMEDIATE FEATURES AND FUNCTIONALITY PART 2

• Understanding the number format codes
• Changing the font color with number format codes
• Hide Number and Text based on criteria
• GOTO SPEICAL Feature
• Creating Name range and Name Manager
• Hyperlink from a different sheet, workbook, cell, defined names, and websites
• Remove Duplicates
• Data Validation: Data List, rules, modify error messages
• Grouping, ungrouping and Subtotal in Excel

NUMBER FUNCTION

• COUNT, COUNTA, COUNTBLANK
• COUNTIF
• COUNTIF with wildcard
• Uses of COUNTIF with real-time example
• Unique Entry using the COUNTIF function
• Find duplicates using COUNTIF Function Running Count

Example of COUNTIFS

Example of SUMIFS Example of AVERAGEIFS Example of DISCOUNT Example of COUNTA Example of DSUM Example of AVERAGE

LOGICAL AND SUBPRODUCTS FUNCTIONS

• Logical Functions: IF, Nested IF, AND, OR, NOT, XOR, IFERROR
• SMALL and LARGE function
• SUMPRODUCT Function
• Use SUMPRODUCT for COUNT
• Use SUMPRODUCT for SUM
• Use SUMPRODUCT for COUNTIF and COUNTIFS
• Use SUMPRODUCT for SUMIF and SUMIFS
• Use SUMPRODUCT for AVERAGE and AVERAGEIFS
• Use SUMPRODUCT for MAXIF and MAXIFS

TEXT FUNCTIONS IN EXCEL TO SOLVE COMPLEX TEXT PROBLEMS

• Introduction of TEXT Functions (LEFT, RIGHT, MID, REPLACE, SUBSTITUTE, etc) available in Excel
• Use text functions to Split the words
• Use text functions to split characters and numbers What-If Analysis in Excel

DATE AND TIME FUNCTIONS IN EXCEL CREATE DYNAMIC CALENDER FOR WHOLE MONTHS AND YEARS

• Introduction of Date Functions available in excel.
• Create and Design Calendar using Different Date Functions (WEEKDAY, WEEKNUM, EOMONTH, EDATE) in excel.
• Calculate Age using DATEDIF Function
• Calculate the date and time difference
• count working days using NETWORKDAYS Function

LOOKUP, VLOOKUP, AND HLOOKUP FUNCTIONS

• Introduction of LOOKUP VLOOKUP and HLOOKUP
• VLOOKUP with Exact Match
• VLOOKUP with Approximate Match
• Usage of VLOOKUP Functions using ROW and COLUMN functions
• Example of VLOOKUP with Array
• Example of HLOOPKUP with multiple Examples
• Example of LOOKUP with multiple examples

INDEX AND OFFSET FUNCTION, USES OF ARRAY

• Introduction of the INDEX Function
• Introduction of OFFSET Function
• Use of INDEX Function
• Advance uses of Array Function using INDEX, OFFSET, MATCH, SMALL, LARGE, ROW, and COLUMN function Use of ROW and ROWS function using INDEX and OFFSET function

DATA ANALYSIS USING POWER QUERY AND POWER PIVOT

• INDIRECT AND ADDRESS FUNCTION
• Introduction of INDIRECT Function
• Introduction of ADDRESS Function
• Uses of INDIRECT Function
• Uses of ADDRESS Function

MIS REPORTS AND DASHBOARDS

• Creating a Pivot table and Pivot chart in Excel
• Adding Slicer and Timeline to analyze in Excel
• Using the power pivot to do the Data Analysis
• Using Power Query to Source the data from Different Sources

CREATING A DYNAMIC DASHBOARD ON LIVE DATA

• Introduction of Dashboards and MIS Reports
• Adding and Using User Form control in Excel
• Recording Macro and run it through a command button

• Creating Dynamic Formula based on` User Form control
• Creating Dynamic Charts using Formula
• Creating 2D charts in Excel
• Importance of charts in Dashboards and MIS Reports

Project work on MIS Dashboard

SQL: INTRODUCTION TO RDBMS AND MICROSOFT SQL SERVER

• INTRODUCTION TO RDBMS CONCEPTS
• NORMALIZATION TECHNIQUE
• SQL SERVER ARCHITECTURE
• INTRODUCTION TO MICROSOFT SQL SERVER
• INTRODUCTION TO SQL SERVER MANAGEMENT STUDIO
• INSTALLATION OF SQL SERVER MANAGEMENT STUDIO

SQL: CREATING TABLES, AND DATABASES AND IMPORTING AND EXPORTING DATA

• INTRODUCTION OF LANGUAGE STATEMENTS (DDL, DML, DCL, TCL)
• CREATING TABLE AND DDL COMMANDS
• INTRODUCTION OF IMPORT AND EXPORT WIZARD

SQL: CONSTRAINTS AND DATA TYPES

• SQL DATA TYPES
• SQL CONSTRAINTS
• SQL INDEXING

SQL: SELECT STATEMENT AND WORKING WITH SORTING AND FILTERING DATA

• SELECT STATEMENT
• SQL DISTINCT KEYWORD
• SQL WHERE CLAUSE
• SQL WILD CARD CONCEPT
• SQL GROUP BY AND HAVING CLAUSE
• SQL CASE STATEMENTS

SQL : WORKING WITH MULTIPLE TABLES USING JOIN CONCEPT

• INTRODUCTION OF JOIN
• INNER JOIN
• OUTER JOIN
• LEFT JOIN
• RIGHT JOIN
• SELF JOIN
• CROSS JOIN
• CONCEPT OF NESTED JOIN WITH WHERE CLAUSE

SQL : USING DML TO MODIFY DATA

• SQL UPDATE STATEMENT
• SQL INSERT STATEMENT
• SQL DELETE STATEMENT
• SQL TRANSACTION CONTROL LANGUAGE

SQL : USING BUILT IN FUNCTIONS

• SQL NUMBER FUNCTIONS
• SQL DATE FUNCTIONS
• SQL TEXT FUNCTIONS
• SQL SPECIAL FUNCTIONS

SQL : USING SUBQUERIES CONCEPTS AND TABLE EXPRESSIONS

• SQL SCALAR SUBQUERIES
• SQL MULTIVALUED SUB QUERIES
• SQL TABLE VALUES SUB QUERIES
• SQL COMMON TABLE EXPRESSION

SQL : USING SET OPERATORS AND WINDOWS RANKING, AND AGGREGATE FUNCTIONS

• SQL UNION, UNION ALL, INTERSECT AND EXCEPT
• SQL CROSS APPLY OPERATOR
• SQL RANK FUNCTION
• SQL DENSE_RANK FUNCTION
• SQL ROW_NUMBER FUNCTION
• SQL OVER CLAUSE

SQL : PROGRAMMING WITH T-SQL AND STORED PROCEDURE

• SQL IF CONDITION
• SQL WHILE LOOP
• CREATING STORED PROCEDURE
• MODIFYING STORED PROCEDURE

SQL :WORKING WITH USER DEFINED FUNCTIONS

• CREATING USER DEFINED FUNCTION
• MODIFYING USER DEFINED FUNCTION
• SQL PIVOT AND UNPIVOT CONCEPT

SQL: APPLYING ERROR HANDLING CONCEPT IN T SQL PROGRAMMING

• IMPLEMENTING T-SQL ERROR HANDLING
• REDIRECTING ERRORS WITH TRY/CATCH
• USING THROW TO PASS AN ERROR MESSAGE BACK TO A CLIENT
• APPLYING ERROR HANDLING IN STORED PROCEDURE

SQL: CONCEPT OF CURSOR AND INDEX

• INTRODUCTION OF CURSOR
• CREATING CURSOR

• MODIFYING CURSOR
• DETAILED DESCRIPTION ABOUT INDEX

SQL: PERFORMANCE TUNING

• QUERY OPTIMIZATION
• PERFORMANCE TUNING

INTRODUCTION TO POWER-BI

• Introduction to Visualization
• Why Business Intelligence
• Introduction to Power BI
• Power BI Architecture
• Components of Power BI
• Power BI Flow
• Introduction of Power BI Desktop
• Power BI Licensing (Free vs Pro vs Premium)
• Power BI Desktop Installation
• Power BI Desktop User Interface and Views

DATA LOADING

• Types of Data Sources in Power BI
• Data Loading Modes and Advantages

DATA TRANSFORMATION1.

• First Row Header
• Duplicate Columns
• Split Columns
• Remove Duplicate/Columns
• Replace Value
• Change Data Type
• Group BY

DATA TRANSFORMATION2.

• Pivot and Unpivot
• Formatting data
• Add Custom Columns
• Invoke Function
• Append Queries
• Join Merge Queries
• Combine Files (Folder)
• Parameters
• Other Query Features

DATA MODELING1.

• Components of Data Modeling
• Introduction to Data Models
• Data Model Engine
• Types of Data Model (Flat, Star & Snowflake)
• Granularity of Data & multiple fact tables
• Data Modeling Relationships
• One to One
• One to Many
• Many to Many

DATA MODELING2.

• Roll Playing Tables
• Sorting/Renaming/Hiding Tables
• Hierarchy Creation
• Formatting & Data Category
• Grouping / Binning
• Drill Through Reports
• What IF Parameter

VISUALIZATION1

• Designer Options
• Power BI Visualization Types
• Custom Visualization
• Best Practices
• Choosing Colors
• Types of Charts and Usage
• Slicers / Filters
• Creating Complete Visualization Report (Practical)

VISUALIZATION1

• Continue Creating Complete Visualization Report (Practical)
• Story Telling with the created Report
• Bookmarks / Selection Pane
• Spotlight
• Drill Through
• Additional Features

SERVICE1

• Publishing / Deploying Reports
• Apps Workspace & Roles
• Creating Dashboard
• Q&A
• Quick Insights
• Dashboard Options
• Subscription
• Alerts

SERVICE2

Export Options (Embed, Web, Power Point…etc.)

• Excel Integration
• Row Level Security
• Data Refresh Concept
• Gateway and Installation
• Power BI Mobile Service
• Power BI Admins Roles
• Audit Logs

DAX1 .

• Introduction DAX
• DAX Data Types
• Calculated Columns
• Calculated Tables
• Calculated Measures
• Calculated Columns vs Measures
• Aggregated Functions (SUM, MIN, MAX, AVG, COUNT)
• Relate Functions (RELATED & RELATED TABLE)
• Iterative Functions (SUMX, AVGX, COUNTX)
• Variables (VAR)
• Logical functions (AND, OR, IF, SWITCH)
• Table Functions (VALUES, FILTER, CALCUILATETABLE)
• FORMAT FUNCTION, FIND, SEARCH, SUBSTITUTE, VALUE, UNION, ROW, ADD COLUMNS, SUMMARIZE
• Handling Blanks (ISBLANK, NOTISBLANK)
• Rank Functions (RANKX)

DAX DATA AND TIME INTELLIGENCE

• CALCULATE (ALL, ALL EXCEPT, ALL SELECTED)
• Understand Row & Table level Filter Context
• Date Functions (DATEADD, DATEDIFF, TODAY Required Date Formats)
• Calendar Functions (CALENDAR, CALENDAR AUTO)
• Time Intelligence functions (TOTALMTD, TOTALQTD, TOTALYTD, PARALLELPERIOD, SAMEPERIODLASTYEAR, PARALLELPERIOD, RUNNING TOTAL)
• SEMI ADDITIVE (OPENING BALANCE, CLOSING BALANCE, LAST DATE, FIRST DATE, LAST NONBLANK, FIRSTNONBLANK)

PROJECT & DISCUSSION(Q&A)

• Project 1
• Project 2

PYTHON INTRODUCTION

• Introduction to Programing Language
• Introduction to Python
• History of Python
• Why Python and Comparison with all languages
• Version of Python

• Environment Setup
  • Discussed about IDE
  • In window
  • Mac

STARTING WITH VARIBLES, COMMENTS

• Variables
• Comments
• Data Types in Python
  • Numbers
  • String
  • List
  • Tuple
  • Dictionary

OPERATOR AND I/O FUNCTIONS.

• Operators
  • I/O Functions
  • Type Conversion

IF CONDITION AND DECISION MAKING

• Basic Introduction
• If, If else, Nested if and elif

LOOP CONCEPT

• Loops
• Basic Introduction
• Need of Loop
• While Loop
• For Loop

FUNCTIONS

• Introduction
• Pre-Define Functions
• User Define Functions
• Lambda Function

DEEP DIVE IN DATA TYPES OR DATA STRUCTURE

• String
• List

• Tuple
• Dictionary

DEBUGGINGH AND ERROR HANDLING

• Section introduction
• Raising our own error
• Try and Except Block
• Try, Except, Else and Finally
• Debugging with PDB

MAKE HTTP REQUEST

• TTP Introduction
• HTTP verbs and API
• Writing your First Python Request
• Requesting Json with Python
• Sending Request with Params
• API Projects

OBJECT ORIENTED PROGRAMMING.

• TTP Introduction
• HTTP verbs and API
• Writing your First Python Request
• Requesting Json with Python
• Sending Request with Params
• API Projects

OOPS EXERCISE.

• Introducing the class Attributes
• Class Methods
• Inheritance and Objectives
• All about the Properties
• Introduction to Super
• Polymorphism Introduction
• Some Special Methods

ITERATORS AND GENERATORS.

• Section Introduction
• Iterators vs Iterables
• Writing own Custom iterators
• Writing our own Version of Loops
• Making a Deck Class Iterable
• Testing Memory Usage with Generators
• Generator Expression and Speed Testing

DECORATORS.

• Higher Order Functions
• Introduction to Decorators

• Decorators with Different Signature
• Using Wraps to preserver Metadata
• Building a Speed Test Decorator

TESTING.

• Section Introduction
• Why Test
• Assertions
• Introduction to Doctest
• Introduction to Unit Test
• Hands on work on Testing

FILE INPUT AND OUTPUT.

• Section Introduction
• Reading the Text File: Open, read and Write
• Reading the File: Seek and Cursor
• The with Statement
• Writing to Text File
• Working on Csv and Pickling
• Reading CSV File
• Writing CSV File

WEB SCRAPING WITH BEAUTIFUL SOUP.

• Introduction to Web Scraping
• Is Scraping ok?
• Selecting with Beautiful Soup
• Navigate with Beautiful Soup
• Web Scraping Projects

REGULAR EXPRESSION.

• Introduction to Regular Expression
• Writing basic Regix
• Regix basic Quantifiers

PYTHON+SQL

• Introduction to the section
• Installing Mysql
• Why Mysql
• Sql Basic: DDl, DML
• Inserting with Python
• Fetching the Data in Python from MySQL

TAKE AWAY FROM THE TRAINING

• There will be Assignment on Each and Every Section
• Hands-on experience on Each and Every Section
• Interview Questions will also be discussed
• By this Content you can get a Job in Python Language
• Project 1
• Project 2