Data Analytics Training in Kovilpatti

Introduction

• The Relational Model

Understanding Basic SQL Syntax

• Basic SQL Commands – SELECT
• Basic SQL Commands – INSERT
• Basic SQL Commands – UPDATE
• Basic SQL Commands – DELETE
• Querying Data with the SELECT Statement
•  The SELECT List
• SELECT List Wildcard (*)
• The FROM Clause
• How to Constrain the Result Set
• DISTINCT and NOT DISTINCT

Filtering results with Where Clause

• WHERE Clause
• Boolean Operators
• The AND Keyword
• The OR Keyword
• Other Boolean Operators BETWEEN, LIKE, IN, IS, IS NOT

Shaping Results with ORDER BY and GROUP BY

• ORDER BY
• Set Functions
• Set Function And Qualifiers
• GROUP BY
• HAVING clause

Matching Different Data Tables With JOINS

• CROSS JOIN
• INNER JOIN
• OUTER JOINs
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• SELF JOIN

Creating Database Table Stamp

• CREATE DATABASE
• CREATE TABLE
• NULL Values
• PRIMARY KEY
• CONSTRAINT
• ALTER TABLE
• DROP TABLE

Introduction to Power BI:

• Get Started with Power BI
• Overview: Power BI concepts
• Sign up for Power BI
• Overview: Power BI data sources
• Connect to a SaaS solution
• Upload a local CSV file
• Connect to Excel data that can be refreshed
• Connect to a sample
• Create a Report with Visualizations
• Explore the Power BI portal

Power BI Desktop:

• Overview of Power BI Desktop
• Data Sources in Power BI Desktop
• Connecting to a data Sources
• Query Editor in Power BI
• Query Ribbon
• Clean and Transform your data with Query Editor
• Combining Data – Merging and Appending
• Cleaning irregularly formatted data
• Views in Power BI Desktop
• Modelling Data
• Manage Data Relationship
• Automatic Relationship Updates
• Template Apps
• Cross Filter Direction
• Create calculated tables and measures
• Optimizing Data Models
• PBIDS Files

Viz and Tiles:

• Overview: Visualizations
• Using visualizations
• Create a new report
• Create and arrange visualizations
• Format a visualization
• Create chart visualizations
• Use text, map, and gauge visualizations and save a report
• Use a slicer to filter visualizations
• Sort, copy, and paste visualizations
• Download and use a custom visual from the gallery

Reports and Dashboards:

• Modify and Print a Report
• Rename and delete report pages
• Add a filter to a page or report
• Set visualization interactions
• Print a report page
• Send a report to PowerPoint
• Create a Dashboard
• Create and manage dashboards
• Pin a report tile to a dashboard
• Pin a live report page to a dashboard
• Pin a tile from another dashboard
• Pin an Excel element to a dashboard
• Manage pinned elements in Excel
• Add a tile to a dashboard
• Build a dashboard with Quick Insights
• Set a Featured (default) dashboard
• Ask Questions about Your Data
• Ask a question with Power BI Q&A
• Tweak your dataset for Q&A
• Enable Cortana for Power BI

Publishing Workbooks and Workspace:

• Share Data with Colleagues and Others
• Publish a report to the web
• Manage published reports
• Share a dashboard
• Create an app workspace and add users
• Use an app workspace
• Publish an app
• Create a QR code to share a tile
• Embed a report in SharePoint Online

Other Power BI Components and Table Relationship:

• Use Power BI Mobile Apps
• Get Power BI for mobile
• View reports and dashboards in the iPad app
• Use workspaces in the mobile app
• Sharing from Power BI Mobile
• Use Power BI Desktop
• Install and launch Power BI Desktop
• Get data
• Reduce data
• Transform data
• Relate tables
• Get Power BI Desktop data with the Power BI service
• Export a report from Power BI service to Desktop

DAX functions:

• New Dax functions
• Date and time functions
• Time intelligence functions
• Filter functions
• Information functions
• Logical functions
• Math-trig functions
• Parent and child functions
• Text functions

Power Query: Text Functions

• Text Functions
• Merge Columns
• Split and Trim
• Upper, Lower and ProperCase
• Prefix and Suffix
• Left, Right and Mid Functions
• Extract Text with Delimiters

Power Query: Date Functions

• Date Functions
• Year, Quarter, Month and Day Functions
• Find Difference between Dates
• Month and Day Name
• Day, Week of Month, Year
• Extract Date, Time in Power Query
• Calculate Age in Power Query
• Day of Year, Quarter, Month in Power Query

Power Query: Number Functions

• Number Functions
• Basic Number Functions in Power Query
• Percentage, Percent Of, Module
• Round Functions
• IsEven, IsODD, Sign in Power Query

Power Query: Append Files

• Append Multiple CSV Files in a Folder
• Append Multiple Excel Sheets, Tables
• Append Excel Sheets or Tables with Different Columns
• Append Multiple Excel Files from a Folder
• Append Different Data Sources

Power Query: Merge Files

• Merge Files and Tables
• Merge Sheets or Tables
• Merge Data from Multiple Excel Files or Workbooks
• Merge Data from Different Data Sources
• Merge Data having Multiple Criteria in Power BI

Power Query: Conditional Columns

• Conditional Column and Column from Example in Power BI
• Column from Examples – Split Text
• Column from Examples – Merge Columns
• Column from Examples – Date
• Column from Examples – Alphanumeric
• Conditional Column – One Column
• Conditional Column – Two columns
• Conditional Column – Compare two columns
• Conditional Column – on Dates

Power Query: Important Topics

• Fill Down in Power BI
• Grouping
• Transpose
• Unpivot
• Data Types
• Replace Errors and Values
• Keep and Remove Rows
• Add, Remove and Goto Columns

Excel Introduction:

• A overview of the screen, navigation and basic spreadsheet concepts
• Understanding workbooks, worksheets, rows, columns, cells
• Various selection techniques

Entering Data:

• Entering, Editing and Deleting Text, Numbers, Dates
• Using Auto Lists
• Moving and Copying data
• Inserting, Deleting and Hiding Rows & Columns
• Inserting, Deleting, Moving and Copying Sheets
• Using navigation techniques

Customizing Excel:

• Customizing the Ribbon
• Customizing the Quick Access Toolbar
• Using and Customizing AutoCorrect
• Changing Excel’s Default Options
• Creating a Custom AutoFill List
• Creating a Custom Number Format
• Customizing Excel Review

Creating And Editing Formulae:

• Concept of Formulae
• Creating Formulae, Editing Formulae
• Bodmas : Mathematical Order
• Copying Formulae
• Using Functions – Sum, Average, Max,Min, Count, Counta
• Applying Absolute (Fixed) Referencing

Formatting And Proofing:

• Formatting Cells with Number formats, Font formats, Alignment, Borders, etc
• Basic conditional formatting
• Copying and Clearing Formats
• Working with Styles
• Spell Check, Find & Replace and AutoCorrect

Mathematical Functions:

• SumIf, SumIfs
• CountIf, CountIfs
• AverageIf, AverageIfs
• SumProduct, Subtotal

LookUp Functions:

• Vlookup / HLookup
• Match
• Dynamic Two Way Lookup
• Creating Smooth User Interface Using Lookup
• Offset
• Index
• Dynamic Worksheet linking using Indirect

Text Functions:

• Upper, Lower, Proper
• Left, Mid, Right
• Trim, Len
• Concatenate
• Find, Substitute

Advanced Paste Special Techniques:

• Paste Formulas
• Paste Formats
• Paste Validations
• Paste Conditional Formats
• Add / Subtract / Multiply / Divide
• Merging Data using Skip Blanks
• Transpose Tables

Sorting:

• Sorting on Multiple Fields
• Dynamic Sorting of Fields
• Bring Back to Ground Zero after Multiple Sorts

Filtering:

• Filtering on Text, Numbers & Date
• Filtering on Colors
• Copy Paste while filter is on
• Advanced Filters
• Custom AutoFilter

Printing Workbooks:

• Working with Themes
• Setting Up Print Area
• Printing Selection
• Branding with Backgrounds
• Adding Print Titles
• Fitting the print on to a specific defined size
• Customizing Headers & Footers

Import & Export Of Information:

• From Web Page
• Exporting to XML
• Creating Dynamic Dashboards and Reports Using Data on Other Applications
• Using Text To Columns

What If Analysis:

• Goal Seek
• Scenario Analysis
• Data Tables

Logical Functions:

• Nested If ( And Conditions , Or Conditions )
• Alternative Solutions for Complex IF Conditions to make work simple
• And, Or, Not

Data Validation:

• Number, Date & Time Validation
• Text Validation
• List Validation
• Handling Invalid Inputs
• Dynamic Dropdown List Creation using Data Validation

Pivot Tables:

• Creating Simple Pivot Tables
• Basic and Advanced Value Field Setting
• Sorting based on Labels and Values
• Filtering based on Labels and Values
• Grouping based on numbers and Dates
• Drill-Down of Data
• GetPivotData Function
• Calculated Field & Calculated Items

Charts & Pivot Charts:

• Bar Charts / Pie Charts / Line Charts
• Dual Axis Charts
• Dynamic Charting
• Other Advanced Charting Techniques

Excel Dashboard:

• Bar Charts / Pie Charts / Line Charts
• Planning a Dashboard
• Adding Tables to Dashboard
• Adding Charts to Dashboard
• Adding Dynamic Contents to Dashboard

VBA Macro:

Introduction to VBA:

• What Is VBA?
• What Can You Do with VBA?
• Recording a Macro
• Procedure and functions in VBA

Variables in VBA:

• What is Variables?
• Using Non-Declared Variables
• Variable Data Types
• Using Const variables

Message Box and Input box Functions:

• Customizing Msgboxes and Inputbox
• Reading Cell Values into Messages
• Various Button Groups in VBA

If and select statements:

• Simple If Statements
• The Elseif Statements
• Defining select case statements

Looping in VBA:

• Introduction to Loops and its Types
• The Basic Do and For Loop
• Exiting from a Loop Advanced Loop Examples

Mail Functions – VBA:

• Using Outlook Namespace
• Send automated mail
• Outlook Configurations, MAPI
• Worksheet / Workbook Operations
• Merge Worksheets using Macro
• Merge multiple excel files into one sheet
• Split worksheets using VBA filters
• Worksheet copiers

Introduction to Python

• What are Python and the history of Python?
• Unique features of Python
• Python-2 and Python-3 differences
• Install Python and Environment Setup
• First Python Program
• Python Identifiers, Keywords, and Indentation
• Comments and document interlude in Python
• Command-line arguments
• Getting User Input
• Python Data Types
• What are the variables?
• Python Core objects and Functions
• Math Module

Control Statements

• If
• If-elif-else
• while loop
• for loop
• Break
• Continue
• Assert
• Pass
• return

List, Ranges & Tuples in Python

• Introduction
• Lists in Python
• Generators and Yield
• Generators Comprehensions and Lambda Expressions
• Next() and Range()
• Understanding and using Range

Python Dictionaries and Sets

• Introduction to the section
• Python Dictionaries
• More on Dictionaries
• Sets
• Python Sets Examples

Input and Output in Python

• Reading and writing text files
• Writing Text Files
• Appending to Files and Challenge
• Writing Binary Files Manually

Python built-in function

• Python Modules & Packages
• Python User defined functions
• Defining and calling Function
• The anonymous Function

Introduction to Data Analytics with Python

• Overview of Data Analytics and its applications
• Python basics for data analytics
• Introduction to Jupyter Notebook for code and data visualization
• Setting up the Python environment and installing key libraries

Data Handling with Pandas

• Introduction to Pandas: Series, DataFrames, and Data Structures
• Importing, exporting, and handling data with Pandas
• Data cleaning: handling missing data, duplicates, and data transformations
• Data aggregation, grouping, and summarization
• Merging and joining datasets

Numerical Computation with NumPy

• Introduction to NumPy and its importance in data analytics
• Working with arrays, array indexing, and slicing
• Mathematical operations with NumPy
• Array manipulations and reshaping
• Random number generation for data simulation

Data Visualization with Matplotlib

• Introduction to Matplotlib for basic plotting
• Customizing charts (labels, colors, styles)
• Plot types: line, bar, scatter, histogram, etc.