Data Analytics – Basic

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
• Magics 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 Customize 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 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:- Getting Started with Power BI Service

• Get Power BI Tools
• Introduction to Tools and Terminology
• Dashboard in Minutes
• Refreshing Power BI Service Data
• Interacting with your Dashboards
• Sharing Dashboards and Reports
• Recap and What’s Next

Module 2:- Getting & Transforming Data with Power BI Desktop

• Introduction to Power BI Desktop
• Getting Data: Excel vs Power BI Desktop & Service
• Data Structure for Q&A
• DirectQuery vs Import Data
• Recap and What’s Next

Module 3:- Modeling with Power BI

• Introduction to Modeling
• Setup and Manage Relationships
• Cardinality and Cross Filtering
• Default Summarization & Sort by
• Creating Calculated Columns
• Creating Measures & Quick Measures
• Recap and What’s Next

Module 4:- Power BI Desktop Visualizations

• Creating Visualisations
• Color Formatting
• Setting Sort Order
• Scatter & Bubble Charts & Play Axis
• Tooltips
• Slicers, Timeline Slicers & Sync Slicers
• Cross Filtering and Highlighting
• Visual, Page, and Report Level Filters
• Drill Down/Up
• Hierarchies
• Reference/Constant Lines
• Tables, Matrices & Conditional Formatting
• KPI’s, Cards & Gauges
• Map Visualizations
• Custom Visuals
• Managing and Arranging
• Drillthrough
• Custom Report Themes
• Grouping and Binning
• Selection Pane, Bookmarks & Buttons
• Recap and What’s Next

Module 5:- Power BI Service Visualization Tools

• Introduction
• Standalone Tiles
• Data-Driven Alerts
• Quick and Related Insights
• Custom Q&A questions

Module 6:- Publishing & Sharing

• Introduction
• Sharing Options Overview
• Publish from Power BI Desktop
• Publish to Web
• Share Dashboard with Power BI Service
• Workspaces and Apps (Power BI Pro)
• Content Packs (Power BI Pro)
• Print or Save as PDF
• Row Level Security (Power BI Pro)
• Export Data from a Visualization
• Publishing for Mobile Apps
• Export to PowerPoint
• Sharing Options Summary

Module 7:- Refreshing Datasets

• Understanding Data Refresh
• Personal Gateway (Power BI Pro and 64-bit Windows)
• Replacing a Dataset
• Troubleshooting Refreshing

Module 8:- Power BI & Excel Together

• Options for Publishing from Excel
• Pin Excel Elements to Power BI
• Connect to Data using Power BI Publisher/Analyze in Excel
• Excel 2016 Publish: Upload and Export to Power BI
• Sharing Published Excel Dashboards