The XL Academy

learning@thexlacademy.com

Advance Excel

Day: 1
✓ Introduction to Excel
✓ Excel Components – Tabs, Ribbons, Workbook, Sheet, Row, Columns, cells…
✓ Understanding of Cells Address, Data Entry & modifications
✓ Movements & various ways to range selections
✓ Data formatting in Excel

Day: 2
✓ Using ‘Go To’ to search specific cells (Blanks, Comment, formula etc..)
✓ Range Copy, Paste & PasteSpecial
✓ Data Sorting
✓ Data Filtering
✓ Data Validations
✓ Remove Duplicate
✓ Text To columns
✓ Find & Replace

Day: 3
✓ Subtotal, Group & Ungroup
✓ Data Protections in Excel (Cells, Sheet, Workbook)
✓ Conditional formatting
✓ What-If analysis (Goal Seek, Tables & Scenario Manager)

Day: 4
✓ Understanding of Excel Operations, formula & Functions
✓ Basic & frequently used functions – Sum, Count, Average, Left, Right,
Mid, Len, Find, Search, Replace, Today, now
✓ Evaluate Formula, Error checking, Trace Precedents, Dependents

Day: 5
✓ Excel Charts
✓ Charts modifications
✓ Advance Chart techniques
The XL Academy
learning@thexlacademy.com

Day: 6
✓ If & Nested If Functions with AND/OR
✓ Logical functions
✓ Text Functions
✓ Data & Time Functions

Day: 7
✓ Lookup & Reference Functions
✓ VLOOKUP, XLOOKUP, MATCH, INDEX, ADDRESS, INDIRECT and many more..

Day: 8
✓ Mathematical functions
✓ Database Functions

Day: 9
✓ Understanding of Pivot table
✓ Pivot Charts
✓ Pivot table formatting
✓ Using formal in Pivot table
✓ Slicer

Day: 10
✓ Getting Started with Power Pivot
✓ Enable and Navigate Power Pivot
✓ Manage Data Relationships
✓ Visualizing Power Pivot Data
✓ Create a Power Pivot Report
✓ Create Calculations in Power Pivot
✓ Working with Advanced Functionality in Power Pivot
✓ Create a Key Performance Indicator
✓ Work with Dates and Time in Power Pivot

Project
The XL Academy
learning@thexlacademy.com

SQL Server Course Contents

✓ Duration: 30Hrs
✓ SQL Overview
✓ RDBMS Concepts
✓ The Foundation Statements of T-SQL
✓ Started with SELECT Statement
✓ Adding Data with the INSERT Statement
✓ SQL Commands
▪ DDL
▪ DML
▪ DCL
▪ DQL

✓ SQL Data Types

✓ Basic Commands
▪ Create Statement
▪ Create Database
▪ Create Table
▪ Alter Statement
▪ Alter Database
▪ Alter Table
▪ Drop Statement
▪ Delete Statement
▪ Update Statement

✓ SQL Operators
▪ Arithmetic Operators
▪ Comparison Operators
▪ Logical Operators

✓ NORMALIZATION
▪ 1NF
▪ 2NF
▪ 3NF

✓ JOINS
▪ INNER Join
▪ LEFT Join
▪ RIGHT Join
▪ FULL Join
The XL Academy
learning@thexlacademy.com

▪ SELF Join
▪ CARTESIAN Join

✓ SQL Constraints
▪ PRIMARY KEY Constraints
▪ FOREIGN KEY Constraints
▪ UNIQUE Constraints
▪ CHECK Constraints
▪ DEFAULT Constraints
▪ NULL Constraints

✓ SQL Clauses
▪ WHERE Clause
▪ ORDER BY Clause
▪ GROUP BY Clause
▪ HAVING Clause
▪ TOP Clause
▪ LIKE Clause
▪ UNION Clause
▪ UNION ALL Clause

✓ Adding More to Our Queries

▪ What Is a Sub-Query?
▪ Building a Nested Sub-Query
▪ Correlated Sub-Queries
▪ How Correlated Sub-Queries Work Correlated Sub-Queries in the
WHERE Clause

✓ The EXISTS Operator

✓ Using EXISTS in Other Ways

✓ System Defined Functions:

▪ AVERAGE
▪ COUNT
▪ MAX
▪ MIN
▪ SUM
▪ SQRT
▪ RAND
▪ CONCAT
▪ GROUP BY
▪ HAVING
▪ TRIM
▪ LENGTH
▪ UPPER
▪ LOWER
▪ REPLACE
The XL Academy
learning@thexlacademy.com

▪ REVERSE
▪ SUBSTRING
▪ STUFF
▪ DATEADD(day,10,@date)
▪ DATEDIFF (day,exp1,exp2)
▪ DATEPART(day,exp)
▪ GETDATE
▪ CONVERT
▪ FORMAT
▪ ISNULL
▪ Top

✓ UDF (USER DEFINED FUNCTIONS)

▪ Scalar Functions
▪ Table-valued Functions

✓ UNDERSTANDING INDEXES
▪ How Data Is Accessed in SQL Server
▪ Creating, Altering, and Dropping Indexes
▪ The CREATE INDEX Statement

✓ VIEWS
▪ Simple Views
▪ Views as Filters
▪ More Complex Views
▪ Dropping Views

✓ STORED PROCEDURES
▪ Basic Syntax
▪ Changing Stored Procedures with ALTER
▪ Dropping Stored Procedures
▪ Handling Errors

✓ Transactions
▪ Properties of Transactions
▪ Commit
▪ Rollback
▪ Save point

✓ USING Sequences
▪ Auto Increment (Identity Field)

✓ Triggers
▪ Auto Generated Tables (INSERTED / DELETED)
The XL Academy
learning@thexlacademy.com

Tableau
▪ Overview Dashboard, Sheets & Menu Items
▪ Combined Axis Chart with Stacked Marks
▪ Combined Axis Chart
▪ Constant Reference Line
▪ Creating and Using Sets
▪ Creating an Interactive Dashboard
▪ Creating A Histogram With Binned Measures
▪ Creating a Local Data Connection
▪ Creating Groups and Hierarchies
▪ Creating Visual Groupings
▪ Custom Dates
▪ Data Filters
▪ Dual Axis Chart
▪ Dynamic Bin Size
▪ Exploring Tableau
▪ Filled Maps
▪ Filtering
▪ Formatting Your View
▪ Geographic Mapping
▪ Heat Map
▪ Highlight Table
▪ Manipulating Strings And Data Type Conversion
▪ Motion Chart
▪ Percent of Total
▪ Running Total of Sales
▪ Scatter Plot
▪ Sharing Work Solution.ppt
▪ Sharing Work
▪ showing gaps in data
▪ Sorting
▪ Top N Filter
▪ Totals and Aggregations
▪ Tree Map
▪ URL Actions
▪ Using a Logic Statement to Create a KPI
▪ Using Date Calculations
▪ Using Discrete and Continuous Dates
▪ Using Logic Statements to Regroup Dimension Members
▪ Variable Increase Amount
▪ Year Over Year Change

Tableau - Advanced
▪ Accounting for Nulls in a Blend
▪ Adding Dashboard Actions
▪ Adding Navigation and Help to Dashboards
The XL Academy
learning@thexlacademy.com

▪ Aggregating Dimensions in Calculations

▪ Background Images
▪ Bar in Bar
▪ Box and Whisker Plot
▪ Building a Dashboard with Filters
▪ Bullet Graph
▪ Calculated Fields and Joins
▪ Calculations and Aggregations
▪ Creating a Data Extract
▪ Dual Axis Map
▪ Dynamic Measure Selection with Parameters
▪ Dynamic Reference Line
▪ Exploring Effect of Primary Data Source Selection
▪ Geocoding Addresses
▪ Joining Tables
▪ Modifying Locations
▪ Moving Average
▪ Nested Top N with Context
▪ Nested Top N with Rank Complex
▪ Nested Top N with Rank Simple
▪ Pareto Chart
▪ Point to Point Mapping
▪ Profitability as Percent of Total
▪ Review
▪ Scope and Direction
▪ String Date Conversion
▪ Trend Lines with Forecast
▪ Trend Lines with Small Multiples
▪ Using a Calculated Field in a Blended View
▪ Using a Parameter to Filter across Sources
▪ Using Rank
▪ Window Average
The XL Academy
learning@thexlacademy.com

Power BI

Class 1: INTRODUCTION TO POWER BI

• Introduction to Power BI - Need, Importance
• Power BI - Advantages and Scalable Options
• History - Power View, Power Query, Power Pivot
• Power BI Data Source Library and DW Files
• Cloud Collaboration 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
• On-premise Data Access and Microsoft On Drive
• Power BI Desktop - Installation, 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

Class 2: CREATING POWER BI REPORTS, AUTO FILTERS

• Report Design with Legacy & .DAT Files
• Report Design with Database 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

Class 3: REPORT VISUALIZATIONS and PROPERTIES

• Power BI Design: Canvas, Visualizations and Fields
• 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 Customized Tables with Power BI Editor
• General Properties, Sizing, Dimensions, and Positions
The XL Academy
learning@thexlacademy.com

• 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. Boarders 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 Class

Class 4 : 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

Class 5 : 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
• Dependent Aggregations, Independent Aggregations
• Automated Records Selection with Tabular Data
• Report Parameters: Creation and Data Type
The XL Academy
learning@thexlacademy.com

• 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 Class

Class 6 : 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: Nested Join and Join Kind Functions
• REPLACE, REMOVE ROWS, REMOVE COL, BLANK - M Lang
• Column Splits and Filled Up / Filled Down Options
• Query Hide and Change Type Options. Code Generation Class

Class 7 : 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 Enumeration Value Generation
• M Language Semitics and Syntax. Transform Types
• IF..ELSE Conditions, Transform Column() Types
• Remove Columns (), Split Columns (), Replace Value ()
• 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 Class

Class 8 : DAX EXPRESSIONS - Level 1

The XL Academy
learning@thexlacademy.com

• Purpose of Data Analysis Expressions (DAX)

• Scope of Usage with DAX. Usability 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, Arithmetic, 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 C

Class 9 : 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
• Calculated Columns and Calculated Measures with DAX

Class 10 : 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 Imports and Visual Schemas
• Cloud and On-Premise Data Sources
• How PowerBI Supports Data Model?
The XL Academy
learning@thexlacademy.com

• Relation between Dashboards to Reports

• Relation between Datasets to Reports
• Relation between Datasets to Dashboards
• Page to Report - Mapping Options
• Publish Options and Data Import Options
• Need for PINS @ Visuals and PINS @ Reports
• Need for Data Streams and Cloud Integration

Class 11 : 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

Class 12 : 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 Schedules
• Workbooks and Alerts with Power BI
The XL Academy
learning@thexlacademy.com

• Dataset Actions and Refresh Settings with Gateway

• Using natural Language Q&A to data - Cortana

Class 13 : 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 Dashboards
• Generating Insights with Cloud Datasets
• Generating 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, DataStream
• Creating New Reports from Cortana, Advantages Class
The XL Academy
learning@thexlacademy.com

Python

Lesson 1
Getting started with Python programming

o Python features
▪ Scope of python
▪ Python products
o Python Download, Installation and Environment Setup
o First python program execution “Hello World”

Lesson 2
Variables, keywords and Operators

o Variables
o Memory mapping of variables
o Keywords in Python
o Operators
▪ Arithmetic operators
▪ Operator precedence
▪ Logical operators
▪ Membership Operators
o Basics I/O and Type casting

Lesson 3
User defined functions

o Decision making statements: if-elif-else

o for’ loop
▪ Repetition using for loop: range() function
o ‘while’ loop
▪ Infinite loop
o Loop control keywords: break, continue, pass

Lesson 4
User defined functions

o Introduction to functions
o Function definition and return
o Function call and reuse
o Function parameters
o Function recipe and docstring
o Programming with functions
The XL Academy
learning@thexlacademy.com

o Namespaces and scope of variable

o Recursive functions
o *args and **kwargs
o Modules and Packages

Lesson 5
Strings

o Introduction to Python ‘string’ data type

o Properties of a string
o String built-in functions
o Programming with strings
o String formatting

Lesson 6
Lists and Tuples

o Introduction to Python ‘list’ data type

o Properties of a list
o List built-in functions
o Programming with lists
o List comprehension
o Introduction to Python ‘tuple’ data type
o Tuples as Read only lists
o Project: Employee Data Management by using List

Lesson 7
Dictionary and Sets

▪ Introduction to Python ‘dictionary’ data type

▪ Creating a dictionary
▪ Dictionary built-in functions
▪ Introduction to Python ‘set’ data type
▪ Set and set properties
▪ Set built-in functions
▪ Project: Banking System project by using Dictionary

Lesson 8
File handing – Part 1

▪ Working with files

▪ File objects and Modes of file operations
▪ Reading, writing and use of ‘with’ keyword
▪ read(), readline(), readlines(), seek(), tell() methods
▪ Handling comma separated value files (CSV file handling)
▪ CSV reading and writing with DictWriter

Lesson 9
File handing – Part 2
The XL Academy
learning@thexlacademy.com

▪ JSON parsing
▪ XML parsing with DOM and ElementTree
▪ Pickling (Pickle file handling)
▪ Project: Fruit Store data management

Lesson 10
Email sending Automation

▪ Understanding SMTP
▪ Sending email with sendmail() function
▪ Sending email using Gmail
▪ Email sending with attachment and MIME
▪ Project: Mass mailer

Lesson 11
Exception Handling in Python

▪ Understanding exceptions
▪ try, except, else and finally
▪ raising exceptions with: raise, assert
▪ Creating your own exception classes

Lesson 12
Regular expression

▪ Pattern matching
▪ Meta characters for making patterns
▪ re flags
▪ Project: (Super market) Extract data and calculate the daily profit

Lesson 13
Object oriented programming with Python

▪ OOPs concepts: Classes and objects

▪ Making of a class and module namespace
▪ Static and instance variables
▪ Deep understanding of self and init ()
▪ Inheritance and Overriding

Lesson 14
Object oriented programming continued

▪ Overloading functions
▪ Operator overloading
▪ Encapsulation: Hiding attributes
▪ Understanding threads
The XL Academy
learning@thexlacademy.com

▪ Multithreading

Lesson 15
Database connectivity with Python

▪ Working with MySQL database

▪ Working with Sqlite3 database

Lesson 16
Tkinter GUI Application

▪ Introduction to Tkinter module

▪ Using root window
▪ Creating frames
▪ Using Labels and Buttons
▪ Using Text and Entry widgets
▪ KM to M converter application