Module 1:- Getting Started with Excel
- Introduction to Excel 2013/2016/2019/Office 365
- Application Interface and Key Components of Excel
- Navigating Through Excel Ribbon Tabs
- Exploring Important Excel Options*
- Live Session Exercise
- Splitting data of Single Column into multiple
- 10 Examples to use Auto-fill and Flash Fill
- Magic of Go-To Special
- Merge/Unmerge Cells & Wrap Text
- Extracting Unique Values & Important Ribbon, General and Data Entry Keyboard Shortcuts
Module 2:- Formatting Essentials
- Formatting Essentials Introduction
- Custom Cell Number Formats
- Custom Date/Time Formats
- Working with Comments / Notes
- Format Painter – A Quick way to copy ‘Formatting Attribute’
- Paste Special
- Table, Table Styles & Formatting
- Freeze Panes
Module 3:- Functions & Formulas
- Introduction to Excel Functions and Formulas
- Basics of Functions & Formulas
- Working with Cell References Types
- Most Used Basics & Advanced Functions & Formulas
- Working with Array Formulas
- Creating Customized Formulas Step-by-Step with Live examples
- Creating and Working with Dynamic Ranges using Function and Excel Table features
- Formulas Debugging / Formulas Auditing
- Types of Formula Errors / Error Handling Tricks
- Text Functions: – CLEAN, CONCATENATE, LEFT, RIGHT, MID, LEN, FIND, SEARCH, SUBSTITUTE, and TEXT, etc.
- Date & Time Functions: – DATE, DAYS, TIME, NOW, WEEKNUM, WORKDAY, and WORKDAY.INTL etc.
- Math & Trig Functions: – INT, MOD, ROUND, ROUNDDOWN, SUMIF, SUMIFS, SUMPRODUCT etc.
- Statistical Functions: – AVERAGE, COUNT, COUNTA, COUNTBLANK, MAX, MIN, LARGE etc.
- Logical Functions: – IF, IFS, AND, OR, and IFERROR.
- Lookup & Reference Functions: – FORMULATEXT, VLOOKUP, HLOOKUP, INDEX, MATCH, INDIRECT, and OFFSET
- Newly Introduced Functions in Recent Version of Excel*: – CONCAT, TEXTJOIN, IFS, SWITCH, DGET, UNIQUE, FILTER, etc.
- Nested Conditions/Customize Formulas*
Module 4:- Data Analysis
- Data Sorting
- Data Filtering
- Named Ranges
- 10 different ways to use Conditional Formatting
- 10 different use of Data Validation
- What-If Analysis
Module 5:- Excel Charts
- Introduction to Excel Charts
- Exploring the most commonly used Charts and Templates
- Basics of Charts
- Selecting Requirement based Charts
- Working with Basic Charts:
- Creating Customized / Advanced Charts
- Creating Dynamic Chart
- Working with Dynamic Interactive Charts in Excel using Drop Down
- Working with Chart Elements, Formatting, Chart Styles, Properties, etc.
Module 6:- Pivot Tables
- Introduction to Pivot Table
- Creating a Pivot Table
- Use of Calculated Fields/Items
- Pivot Table Formatting
- Grouping Items & Summarizing data in Pivot Tables
- Grouping and Bucketing data in Pivot Table
- Changing/Modifying Data Sources
- Working with Pivot Table Designs & Layouts
- Exploring Important Pivot Table Options & Field Settings
- Pivot Table Filters
- Changing Pivot Table Summary Calculation
- Use of Slicers in Pivot Table
- Using Source Data to Convert into Infographic Summary
- Introduction to Pivot Charts
Module 1:- Getting Started with My SQL
- An Introduction and Overview of MySQL
- Installation and GUI Tools
- An Overview
Module 2:- My SQL Fundamentals
- Introducing SELECT statement
- Introducing WHERE clause
- Sort result with ORDER BY
- Using FROM to specify the source tables
- Importance of Clause Orders
- Data Modification tricks
Module 3:- Creating Database & Tables
- Creating a database
- Creating a table
- Creating Indexes
- Controlling column behavior with constraints
- Using foreign key constraints
- Creating an ID column
- Changing a schema with ALTER
- Introducing NULL and NOT_NULL
- Introduction to MySQL Data Types
- Setting up default values
- MySQL Warnings
- Alerting a table
Module 4:- My SQL Functions & Clause
- Introduction to MySQL Functions
- String Functions – CONCAT, SUBSTRING, REPLACE, REVERSE, LENGTH, UPPER, LCASE etc.
- Aggregate Functions – COUNT, MIN, MAX, SUM, AVG, ROUND etc
- Date/Time Functions – CURDATE, CURTIME, CURRENT_DATE, LOCALTIME etc.
- Control Flow Functions – IF, IFNULL, NULLIF etc.
Module 5:- Multiple Tables & Joins
- Introduction to JOINS
- Different types of JOINS
- JOINS and Aliases
- Multiple Table Joins
- Creating a simple Subselect
- Understanding of Primary keys and Foreign keys
Module 6:- Transactions, Stored Routines & Triggers
- Transactions & Stored Routines
- Triggers
Bonus Modules
- Creating a New User Login
- Granting access to new users
- Backup and Restore databases
- Important Keyboard Shortcuts Guide
- Session Study Material
- Situational Case Studies for Best Practice and Getting Ready for Corporate World
- 6 Months Live Support via Phone/Email/Messages
Module 1:- Introduction to Power BI
- What is Data Analytics & Data Visualization
- Introduction to Data Analysis
- Introduction to Data Visualization
- What is Business Intelligence?
- Overview of Self-Service Business Intelligence (SSBI) Tools
- Leading Self-service tools
- Comparison of Leading Visualization Tools
- Getting Started with Power BI
- Introduction to Microsoft Power BI
- Why Power BI
- Power BI Elements
- Basic Components of Power BI
- Building Blocks of Power BI
- Key Benefits of Power BI
- Hands-On
- Skills Gained
- Understanding of Data Analysis & Visualizations
- Concepts of Business Intelligence
- Installing Power BI on the System
- Elements and Building Blocks of Power BI
Module 2:- Getting Data from Different Data Sources
- Power Bi Desktop (Getting Data)
- What is Power BI Desktop
- Quick Walk-through Power BI Interface
- Views in Power BI Desktop
- Report View
- Data view
- Model view
- Understanding ETL Concepts: Extract, Transform & Load
- Data Sources in Power BI Desktop
- Connecting and Getting data from different sources in Power BI
- Getting data from Excel, CSV, Access, etc
- Saving Workfile
- Hands-On
- Identifying and retrieving data from different data sources
- Extracting and Loading data into Power BI
- Preparing Data
- Skills Gained
- Concepts of ETL tool
- Getting data from multiple sources
Module 3:- Clean, Transform and Load the Data
- Shaping Data using Power Query
- Loading data into Power BI Desktop
- What is Query Editor
- Cleaning Data with Query Editor
- Transforming Data with Query Editor
- Unpivoting Columns
- Eliminating Rows / Columns
- Changing / Modifying Data Types
- Adding Custom Columns
- Replacing Values / NULL
- Promoting / Demoting Row as Header
- Modifying and managing existing ‘Steps’
- Extracting Date Components from Date-Time
- Introduction to “M Query”
- Combining Data
- Append Merge / Joins, Transpose & Formatting Data Operations
- Hands-On
- Cleaning Data using Power Query Editor
- Shaping the data
- Transforming and cleaning the data
- Merging Rows / Columns from multiple tables
- Joins / Append activities
- Skills Gained
- Data Transformation using Query Editor
- Loading the data
- Understanding of “M Query”
Module 4:- Design a Data Model
- Introduction to Data Modeling
- Understanding of Relationship
- What is Relationship?
- One to Many Vs One to One Relationship
- Auto-Detect Relationship during Load
- Dimension Table Vs Fact Table
- Cardinality in Data Modeling
- Creating Relationship Manually
- Managing Data Relationship
- Editing Relationship
- Creating Calculated Columns
- Creating Measures
- Optimizing Data Models for Better Visuals
- Cross Filter Direction
- Defining Hierarchies
- Hands-On
- Creating Model Relationships
- Managing Relationships
- Creating Measures
- Creating hierarchies
- Skills Gained
- Understanding the basics of data modeling
- Defining relationships and their cardinality
- Understand star schema and its’ importance
- Managing relationships
Module 5:- DAX Formulas & Calculations
- Introduction to Data Analysis Expression (DAX)
- Importance of DAX
- Data Types in DAX
- Defining Calculation Type
- Calculated Column Vs Calculated Measures
- Adding New Measures to Report
- Creating Calculated / DAX Tables
- DAX Syntax & Operators
- Most Important & Common Basics & Advanced DAX Functions
- Aggregate Functions
- Logical Functions
- Time-Intelligence Functions
- Information Functions
- DAX Variables
- Formatting DAX Code
- Handling Errors in DAX Expressions
- Hands-On
- Creating Calculated Tables
- Creating Calculated Columns and Measures
- Writing DAX calculations to perform Data Analysis
- Beautifying DAX
- Creating Variables
- Skills Gained
- Understanding DAX
- Create Calculated Columns, Measures & Tables
- Working with Advanced functions e.g. Time-Intelligence, Filter contexts etc.
Module 6:- Data Visualization & Creating Reports
- Introduction to Visuals in Power BI
- Creating Visualization
- How to use Visual
- Exploring Visualizations’ List
- Exploring Most Common & Important Visualizations
- Bar, Column, Line and Area Charts
- Pie, Donut, and Gauge Charts
- Single Number Cards and Multi Row Cards
- Table & Matrix Visuals
- Combo Chart, Funnel and Treemap Charts
- Slicer, KPI and Custom Visuals
- Map & Filled Map Visuals
- Modifying Color Properties of Charts and Visuals
- Shapes, Text Box, Buttons, and Images
- Types of Filters
- Slice and Dice Data in Power BI
- Drilling-Up/Down
- Understanding Custom Visuals & How to add them
- Customizing Canvas
- Setting up Canvas & Must Know Global Fonts
- Page Layout & Formatting
- Bookmarks in Power BI
- Reports in Power BI
- Conditionally formatting tables/matrixes
- Activity: Creating Sales Report
- Hands-On
- Creating visual and charts
- Designing a report
- Managing visual fields and format properties
- Skills Gained
- Creating and selecting effective visualizations
- Designing report page layout and Visual modification
- Adding report navigation & basic functionalities
- Creating visually stunning reports
Module 7:- Power BI Service and Managed Workspaces
- Introduction to Power BI Service & Workspaces
- What is Power BI Service
- Exploring Interface of Power BI Service
- Understanding the Admin Portal settings
- What is Power BI Workspace
- Understanding of “Workspace”
- My Workspace Vs New Workspace
- Creating New Workspace
- Sharing and Managing Workspaces
- Roles in workspaces in Power BI
- Exploring Power BI Licensing
- Power BI Free Vs Power BI Pro Vs Premium
- Hands-On
- Publishing reports on Power BI Service
- Creating workspaces
- Sharing and Managing Reports
- Moving important assets to App and Publishing app in Power BI
- Skills Gained
- Knowledge of Power BI Service (Cloud)
- Workspaces in Power BI
- Creating and managing a workspace
Module 8:- Creating Dashboard in Power BI
- Dashboard in Power BI
- Introduction to Dashboard
- Difference between Report Vs Dashboard
- Preparing and Creating Dashboard
- Configuring a Dashboard
- Pinning Visuals to Dashboard
- Dashboard Tiles
- Dashboard Widgets
- Introduction to Power BI Q&A
- Asking questions about data
- Fetching results using Q&A
- Using Q&A to create a dashboard tile
- Quick Insights in Power BI
- Data Analysis using Quick Insights
- Sharing and Collaborating Dashboard with Business Users
- Hands-On
- Creating a Dashboard
- Pinning visuals to Dashboard
- Adding text/video widgets to Dashboard
- Sharing and Moving Dashboard to distributed app
- Use of Power BI Q&A to question your data
- Skills Gained
- Dashboard creation from report
- Enhancing dashboard usability
- Importance of Q&A and Quick Insights
Module 9:- Data Gateway, Security & Schedule Refresh
- Report Security in Power BI
- Introduction to Row-level Security (RLS)
- Setting up and Enforcing Row-level security
- Implementing Row-level security
- Data Gateway & Report Scheduling
- What is Data Gateway
- Types of Gateway
- On-premises data gateway (personal mode)
- On-premises data gateway (standard mode)
- Installing Data Gateway
- Data Gateway System Requirement
- Benefits of Data Gateway
- Scheduled Refresh & Refreshing Data
- Managing Data Source
- Adding and removing a Data Source
- Hands-On
- Configuring & Implementing RLS
- Managing datasets
- Setting up On-Premise Data Gateway
- Connecting Reports on Power BI Cloud with Local Data Files
- Schedule dataset refresh
- Skills Gained
- Dashboard creation from report
- Enhancing dashboard usability
- Importance of Q&A and Quick Insights
Bonus Module:- In-Class Project
- In-Class Project: Building Sales Report & Dashboard from Scratch
- Extracting Data from the Local Source
- Transforming Data using Power Query
- Cleaning, Shaping and Preparing the data if necessary
- Loading Data to Power BI Desktop
- Building Data Model
- Visual and Charts
- Row-level Security
- Publishing Reports
- Power BI Service
- Creating Dashboard
- Hands-On
- Analyzing Sales data
- Derive conclusions from the patterns and trends shown in the visualization
- Skills Gained
- Report and Dashboard creation
- Publishing, sharing and collaborating it with Business users
Module 1:- Getting Started with Tableau
- Introduction to Data Visualization
- Leading Data Visualization Tools
- Introduction to Tableau
- Exploring Interface and Important Key Component
- Navigating Through Tableau Menu Tabs
- Exploring Each Menu Tab i.e. File, Data, Worksheet, Dashboard, Story, Analysis, Map, Format, Server etc.*
- Tableau – Design Flow
- File Types
- Tableau Data Types
- Show Me
- Data Terminology
Module 2:- Connecting to Data with Tableau Desktop
- Introduction to Data Connection
- Data Source Interface
- Types of Data Connections
- Extracting Data
- Custom Data View
- Joins and Unions
- Data Blending
- Live Connection Vs Extract
- Field Operations
- Basic Project Activity
Module 3:- Examining & Filtering
- The Sheet Interface
- Dimensions & Measures
- Hierarchies
- Data Granularity
- Highlighting
- Data Sorting
- Grouping Data
- Data Filtering
- Data Source Filters
- The Filter Shelf
- Dimension Filters & Card Modes
- Context Filters
- Measure Filters
- Creating Sets
Module 4:- Field Types & Charts
- Utilize Auto-Generated Fields
- Use Titles, Captions and Tooltips Effectively
- Creating Bins
- ToolTip
- Basic Charts
Module 5:- Calculations in Tableau
- What are Calculations
- Methods to Create Calculated Field
- Introduction to Tableau Functions
- Operator and Syntax Conventions
- Introduction to Table Calculations
Module 6:- Level of Detail (LOD) Expression
- Level of Detail (LOD) Calculations
- Live Use Cases of LOD
- Introduction to Parameters
- Parameters Data Type Options
Module 7:- Geographical Visualization
- Introduction to Geographic Visualizations
- Assigning Geographical Locations
- Spatial Files
- Map Types
- Custom Geocoding
- Background Image
Module 8:- Advanced Charts in Tableau
- Introduction to Advanced Charts
- Bar in Bar Chart
- Bullet Chart
- Pareto Chart
- Gantt Chart
- Hierarchy and Tree Maps
- Box and Whisker’s Plot
- Waterfall Chart
- Step and Jump Lines
- Maps on a Scatter Plot
- Bubble Chart
- Control Chart
- Funnel Chart
- Packaged Bubbles
- Word Cloud
- Donut Chart
- Trendlines
- Reference Line, Bands, and Distributions
Module 9:- Dashboard & Stories
- Introduction to Dashboards
- The Dashboard Interface
- Important Dashboard Objects
- Adding Objects to the Dashboard
- Building a Dashboard
- Dashboard Design and Formatting
- Types of Actions
- Designing Dashboard for Tablets & Mobile-Phones
- Story Points
- Sharing Workbook
- Wrapping up Tableau Program
Module 1: Getting Started with Python Core
✓ Need of Programming with an Example
✓ Why Programming
✓ Advantages of Programming
✓ Different Programming Languages
✓ Introduction to Python
❑ A Brief History of Python
❑ Why Python
✓ Installing Python
✓ Creating Python File using IDLE
✓ Write your first Program in Python
✓ How to execute Python Program
✓ Identifier
❑ Rules for Naming Identifiers
✓ Variables
✓ Operator
❑ Operator Types
✓ Q&A
Module 2: Datatypes in Python
✓ Introduction to Python Data Types
✓ Strings
❑ Introduction to Python ‘String’ data type
❑ String Properties
❑ String built-in functions
❑ Programming with Strings
❑ String Formatting
✓ Lists and Tuples
❑ Introduction to Python ‘List’ data type
❑ List Properties
❑ List built-in functions
❑ Programming with Lists
❑ List Comprehension
❑ Introduction to Python ‘tuple’ data types
❑ Tuples as Read only lists
✓ Dictionary and Sets
❑ Introduction to Python ‘Dictionary’ data type
❑ Creating a dictionary
❑ Dictionary built-in functions
❑ Introduction to Python ‘set’ data types
❑ Set and Set properties
❑ Set built-in functions
✓ Q&A
Module 3: Conditional & Control Statements in Python
✓ Introduction to Conditional Statements
❑ Types of Conditional Statements
o If….Statement
o If….Else Statement
o Elif…. Statement
✓ Introduction to Loops
❑ Types of Loops in Python
o While….Loop
o For….Loop
o Nested Loop
✓ Introduction to Loop Control Statements
❑ Loop Control Statements Keywords
o Break Statement
o Continue Statement
o Pass Statement
✓ Q&A
Module 4: Functions in Python
✓ Introduction to Python Functions
✓ User Defined Functions
❑ Functions definition and return statement
❑ Calling a Function
❑ Parameters and Arguments
❑ Required Arguments
❑ Default Argument
✓ Variable Scope in Function
❑ Local Scope
❑ Global Scope
❑ Enclosing Scope
❑ Built-in Scope
✓ Modules and Packages
❑ Importing Module (from, import statement)
✓ Anonymous functions (Lambda)
✓ Q&A
Module 5: Exception Handling & OOPs Concepts
✓ Getting started working with Files
❑ File Objects and Modes of file operations
❑ Reading, Writing, and use of ‘With’ Keyword
❑ Read(), Readline(), Readlines(), Write(), Writeline()
✓ Introduction to Exception Handling
❑ Understanding Exceptions
❑ Handling An Exceptions
❑ Try, Except, Else, and Finalizing
❑ Raising Exceptions with: Raise, Assert
✓ Introduction to Object Orientated Programming (OOPs)
❑ Why OOPs
❑ Difference Between POPs and OOPs
❑ OOPs Concepts
❑ Python OOP Vs Other OOPs
❑ Class and Objects
❑ Relation Between Class and Objects
❑ Creating a Class
❑ Attributes
✓ Built-In Class Attributes
✓ Class Variable and Instance Variable
❑ Constructor and Destructor
❑ Multiple Constructors
❑ Abstraction
❑ Inheritance
✓ Inheritance Types
❑ Overloading
❑ Overriding
❑ Data Hiding
✓ Q&A
Module 6: Database Connectivity & Regular Expressions
✓ Introduction to Regular Expressions
❑ What are Regular Expressions
❑ Regular Expressions Operations
www.analyticstraininghub.com © 2016-2021 | Analytics Training Hub ( A Unit Of Medhya Analytics Solutions Pvt. Ltd.)
❑ Search Function
❑ Match Function
❑ Modifiers
❑ Patterns
✓ Database Connectivity
❑ Introduction to Database Connectivity
❑ Connections
❑ Executing Queries
❑ Transactions
✓ Q&A
Module 7: Data Manipulation & Data Visualization
✓ What is Data Manipulation
✓ Introductions to Pandas
✓ Data Manipulation with Pandas
✓ Data Structures & Series
✓ Data Frame
✓ Missing Values
✓ Data Operations
✓ Data Standardizations
✓ Pandas File Read (CSV, Excel, SQL) and Write Support
✓ Data Acquisition (Import & Export)
✓ Introduction to Data Visualization using Matplotlib
❑ Installing Matplotlib
❑ Plotting in Matplotlib
❑ Creating First Plot with Matplotlib
❑ Creating Column/Line/Scatter Plots
✓ Wrapping Python Core Program
✓ Q&