EXCEL AND Advance Excel 

• • Introduction to excel

  • Excel worksheet and workbooks

  • Entering and editing worksheet data

  • Introducing formulas and function

  • Customizing common option in excel

  • Absolute and relative cells

  • Protecting and un-protecting worksheet and cells

  • Lookup

  • Vertical and horizontal lookup

  • Double v lookup with if error formula

  • V LOOKUP with exact match , approximate match

  • V lookup using tables, dynamic ranges

  • Using VLOOKUP to consolidate data from multiple sheets

  • Use if and lookup function together in excel

  • Using v lookup and if error

  • V lookup exact match with N/A error to fix

  • V lookup v/s index and match

  • Specifying a valid range of values for a cell

  • Specifying a valid values for a cell

  • Specifying custom validation based on formula for a cell

  • sorting tables

  • using advance filter option

  • using multiple –level sorting

  • pivot tables

  • pivot charts

  • creating pivot tables

  • formatting and customizing pivot tables

  • pivot table using subtotal

  • using advance option of pivot tables

  • show value as (%of row, % of column, % of running total, compare with specific field)

  • pivot using concatenate function

  • viewing subtotal under pivot

  • creating slicers

  • pivot table using index

  • consolidating data from multiple sheets and files using pivot tables 

  • date and time functions

  • text function

  • database functions

  • power functions(count if, count ifs, sum if, sum ifs) 

  • Goal seek

  • Data tables

  • Scenario manager

  • Using charts

  • Using 3d graphs

  • Using bar and line chart together

  • Emi calculation

  • Present value and future value calculation

  • Loan calculation

  • Bank pass book

  • Attendance with salary sheet

  • Salary increase sheet

  • Pay slip

  • Gst calculation

  • Calculated sales

  • Human age calculation

  • function (length ,right , left, concatenate, trim, upper, lower, mid, find ,rept, char)

  • Count function(counta ,countblank ,count)

 PYTHON

• What is Python?

• The Birth of Python

• History of Python

• Features of Python

• Versions of Python

• Installation of Python

• Python Documentation

• Getting Help

• Python Editors and Ides

• Basic Syntax

• Keywords

• Data Types

• Variables

• Naming Conventions

• Print( )

• Type( )

• Input( )

• Del Keyword

String Handling:

• What is String?

• Single-quoted string literals

• Triple-quoted string literals

• String Indexing

• String Slicing

• Working with String Functions

• Working with String Methods

Operators:

• Arithmetic Operators

• Logical Operators

• Comparison operator

• Assignment Operators

• Bitwise Operators

• Membership Operators

• Identity Operator

Flow Control:

• Conditional Statements 

• Looping Statements 

• Break statement

• Continue statement

• Pass statement

List and tuple:

• About Sequences

• Lists

• Tuples

• Indexing and Slicing

• Iterating through a sequence

• Functions for all sequences

• Operators and keywords for sequences

• The range( ) function

• List comprehensions

Dictionaries and Sets:

• About Dictionaries

• Creating Dictionaries

• Getting dictionary values

• Accessing the dictionaries value

• Iterating through a dictionary

• About sets

• Creating sets

• Accessing the set value

• Iterating the set

Functions:

• Defining a function

• Calling a function

• Function with Parameters

• Function without Parameter

• Type of arguments

• Returning Values

• Variables scope

• Lambda function

• Filter function

• Map function

• Reduce function

Modules:

• What is a module?

• Creating user defined module

• Setting path

• The import statement

• Module search path

• From … Import

• Module Aliases

• Dir function

• Working with Standard modules

(Math, Random, Date and time, os)

Errors and Exception Handling:

• • Syntax Errors

  • What is Exception?

  • Need of Exception handling

  • Predefined Exceptions

  • Except block

  • Try block

  • finally block

  • else block

  • Handling Multiple Exceptions

  • User defined Exceptions

File Handling:

• Opening a file

• Closing a file

• Writing data to files

• Reading a data from files

OOPs concepts:

• About OO programming

• Defining Classes

• Creating object

• Class methods and data

• Constructors

• Inheritance

• Polymorphism (method overloading & method overriding)

• Encapsulation

 SQL

Users and Schemas

• • Introduction to Users and Schemas

  • Definitions and Client Tools

  • SQL Developer Login

  • Creating An Application Schema

  • SQL COMMANDS

SQL command

• TCL --- Transaction control language

• DML --- Data manipulation  language

• DCL --- Data control language

• DDL ---- Data definition language

DDL command

• create statement

• alter

• truncate

• comment

• drop

DCL command

• grant

• revoke

DML command

• select

• insert

• update

• delete

TCL command

• savepoint

• commit

• rollback

Subqueries

• Correlated Subqueries

• Scalar Subquery

• all any operator

Join 

• natural join

• self join

• cross

• inner join

• left join

• right join

operators

• and

• or

• > ,<,>=,<=,<>,=

• between

• like

• in , not in

• not

• having clause

• order by

• group by

Set Operators

• Set Operators Defined

• Relationship to Mathematical Set Theory

• Restrictions on Set Operators

Conditional processing

• • The DECODE Function

  • The Case Expression

SQL Function

• What Are SQL Functions?

• Character Functions

• CONCAT

• LENGTH

• INSTR

• REPLACE

• UPPER

• LOWER

• LPAD

• RPAD

• TRIM

• TO_CHAR

• LEFT , RIGHT

• MID

• SUBSTRING

SQL Functions (Non-Character)

• Numeric Functions

• Date/Time Functions

• Arithmetic Functions

• Null Value Functions

power bi desktop

• Introduction

• What is PowerBI

• How to install

• Project Setup

• How to connect Files

• Query Editor and How it works

• What is Data Model & How to create your first Visual

• Understanding PowerBI Desktop workflow

• Understanding the interface Data Model & Query Editor

• Project Files & Recommended Settings

• Connect Source files to PBI-Desktop

• Data Transformation

• Appending Queries

• Data Formatting & Handling Errors

• Pivot & Unpivot Data

• Split data

• Create Groups & Clean Up Project

• Star Schema

• How to use it in Project

• Query Duplicates & References

• Creating Dimension Table

• Merging Queries

• Join Kind

• Creating Fact Table

• Introduction to Relationships

• Cardinality & Types

• Cross filter Direction & Active Properties

• What is M Language & DAX

• Difference between M & DAX

• Basics of DAX

• DAX & Data Types

• Operators & DAX Functions

• Calendar Function

• IF & Related

• FILTER

• COUNTROWS

• ALL & ALLEXCEPT

• TOPN

• ADDCOLUMN

• SUMMARIZE

• EARLIER

• CALCULATE & CALCULATABLE

• USERELATIONSHIP

• Basic visual Concepts

• Creating first visuals

• Tooltips interactions

• Drill modes & Hierarchical

• Data Color & Conditional formatting

• Formatting reports

• Slicer & Its use

• Summarization & Sorting

• Tree maps & Tables

• Filter Types

• Working with row cards

• Data Gateways and Schedule Refresh

• Row Level Security

• Row Level Security in Power BI Desktop

• Q&A Natural Language Processing option in Power BI

• Quick Insights