Data Analytics With Tableau And Power Bi

```
Introduction
```
MS Office Versions (similarities and differences) Interface (latest available version) Row and columns Keyboard shortcuts for easy navigation Data entry (fill series) Find and select Clear options Ctri+enter Formatting options - Font, Alignment, Clipboard (copy, paste special)

   Lab: Data entry, Fill series options, Copy Paste Special

```
Referencing, Named ranges, Uses, Arithmetic Functions
```
Mathematical calculations with cell referencing (Absolute, Relative, Mixed) Arithmetic functions (SUM, SUMIF, SUMIFS, COUNT, COUNTA, COUNTIFS, AVERAGE, AVERAGEIFS, MAX, MAXIFS, MIN, MINIFS) Functions with name range

   Lab: Arithmetic and statistical functions

```
Logical functions
```
IF AND NOT OR IFERROR Usage of mathematical and logical functions nested together NESTED IFS

   Lab: Logical functions

```
Referring data from different tables
```
LOOKUP INDEX VLOOKUP INDEX WITH MATCH FUNCTION NESTED VLOOKUP INDIRECT HLOOKUP OFFSET

   Lab: Lookup and reference functions

```
Advanced functions
```
Combination of arithmetic, logical, lookup functions Data validation (With dependent drop down)

   Lab: Advanced functions

```
Date and Text Functions
```
Date functions: DATE, DAY, MONTH, YEAR, YEARFRAC, DATEDIFF, EOMONTH Text functions:TEXT, UPPER, LOWER, PROPER, LEFT, RIGHT, SEARCH, FIND, MID, TTC, Flash fill

   Lab: Date and text functions

```
Data Handling
```
Number formatting (with shortcuts) SORT Advanced sort FILTER Advanced Filter CTRL+T (converting into an excel table) Formatting table Remove duplicate

   Lab: Data cleaning, remove duplicates, formatting And filtering

```
Data Visualization: Conditional formatting, Charts
```
Conditional formatting (Icon sets/Highlighted colour sets/Data bars/custom formatting) Charts: Bar, column, lines, scatter, combo, Gantt, waterfall, pie

   Lab: Data Visualization: Conditional formatting

```
Data Summarization: Pivot Report and Charts
```
Pivot Reports: Insert, Interface, Crosstable Reports, Filter, Pivot Charts Slicers: Add, Connect to multiple reports and charts Calculated field Calculated item

   Lab: Data Visualization: Conditional formatting

```
Data Summarization: Dashboard Creation, Tips and Tricks
```
Dashboard: Types, getting reports and charts together, use of slicers Design and placement: Formatting of tables, charts, sheets, proper use of colours and shapes

   Lab: Data Summarization: Dashboard creation

```
Connecting to Data: Power query, pivot, power pivot within Excel
```
Power Query: Interface, Tabs Connecting to data from other excel files, text files, other sources Data cleaning Transforming Loading data into Excel query Using loaded queries Merge and append Similarities and differences in pivot and power pivot reporting Getting data from databases, workbooks, webpages

   Lab: Connecting to data: Power query Getting data from databases, workbooks, webpage queries, merge, pivoting Getting data from databases, workbooks, append

```
VBA and Macros
```
View tab, add developer tab Record macro: Name, storage Record macro to format table (Absolute Ref) Format table of any size (Relative ref) Play macro by button, shape, as command (in new tab) Editing macros VBA: Introduction to the basics of working with VBA for Excel: Subs, ranges, sheets Comparing values and conditions, if statements and select cases. Repeat processes with For loops and Do While or Do Until loops Communicate with the end-user with message boxes and take user input with input boxes, user form

   Lab: Record macro Interface, Sub-end sub,variable, objects Message boxes, user form

```
Introduction to MySQL
```
Introduction to Databases Different types of RDBMS Introduction to RDBMS Software Installation (MySQL Workbench) Explain RDBMS through normalization

```
SQL Commands and Data Types
```
Types of SQL Commands (DDL, DML, DQL, DCL, TCL) and their applications Data Types in SQL (Numeric, Char, Datetime)

```
DQL & Operators
```
SELECT: LIMIT, DISTINCT, WHERE, AND, OR, IN, NOT IN, BETWEEN, EXIST, ISNULL, IS NOT NULL, Wild Cards, ORDER BY

```
Case When Then and Handling NULL Values
```
Usage of Case When Then to solve logical problems and handling NULL Values (IFNULL, COALESCE)

```
Group Operations & Aggregate Functions
```
Group By, Having clause String functions COUNT, SUM, AVG, MIN, MAX, COUNT Date & Time function

```
Constraints
```
INOT NULL DEFAULT UNIQUE Primary key CHECK Foreign Key (Both at column level and table level)

```
Joins
```
Inner Cross left Self joins Right Full outer join

```
DDL
```
Create truncate Drop Modify Alter Comment Rename

```
DML & TCL Commands
```
DML Insert, Update, and Delete TCL Commit, Rollback, Savepoint, and Data Partitioning Drop Modify Alter Comment Rename

```
Indexes and Views
```
Indexes (Different Type of Indexes) and Views in SQL

```
Stored Procedure
```
Procedure with IN Parameter Procedure with OUT parameter Procedure with INOUT parameter

```
Union, Intersect, Sub-query
```
Union Sub Queries Union all Multiple Query Intersect

```
 Introduction to Tableau
```
What is Tableau ? Data Types, Dimensions, Measures, Aggregation concept What is Data Visualization ? Tableau Products Tableau Desktop Installation Data source Overview Tableau Desktop Variations Tableau File Extensions live Vs Extract

```
 Basic Charts & Formatting
```
Overview of worksheet sections Text Table, Highlight Table Shelves Formatting: Remove grid lines, hiding the axes, conversion of numbers to thousands, millions, Shading, Row divider, Column divider Bar Chart, Stacked Bar Chart Discrete & Continuous Line Charts Symbol Map & Filled Map Marks Card

   Lab: Bar chart for Sub-category wise SUM(Profit) with positive values green colour and negative values red colour.

```
 Filters
```
What are Filters ? Order of operation of filters Types of Filters Cascading Extract Dimension, Measure, Quick Filters Data Source Context Apply to Worksheets

   Lab: Show Bottom 3 Products for Apple

```
 Calculations
```
Need for calculations Types: Basic, LOD's, Table Examples of Basic Calculations: Aggregate functions, Logical functions, String functions, Tableau calculation functions, numerical functions, Date functions LOD’'s: Examples Table Calculations: Examples

   Lab: YTD/QTD/MTD Change, Adding, icons

```
 Data Combining Techniques
```
What are Data Combining Techniques ? Types Joins, Relationships, Blending, and Union

```
 Custom Charts
```
Dual Axis Lollipop Chart Combined Axis KPI Cards (Simple) Donut Chart KPI Cards (With Shape)

   Lab: Donut Chart

```
 Groups, Bins, Hierarchies, Sets, Parameters
```
What are Groups? Purpose What are Bins? Purpose What are Hierarchies? Purpose What are Sets? Purpose What are Parameters? Purpose and examples

     Lab: Parameters in Filters/Sets/Bins

```
 Analytics & Dashboard
```
Analytics & Dashboard Trend Line Overview of Dashboard: Tiled Vs Floating All Objects overview, Layout overview Dashboard creation with formatting

     Lab: Same Dashboard with background image

```
 Dashboard Actions & Tableau Public
```
Actions: Filter, Highlight, URL Sheet, Parameter, Set How to save the workbook to Tableau Public website ?

     Lab: Filter Action

```
 Power BI Introduction and Installation
```
Understanding Power BI Background Installation of Power BI and checklist for perfect installation Formatting and Setting prerequisites Understanding the difference between Power BI desktop & Power Query

```
 The Power BI user interface
```
Getting familiar with the interface BI Query & Desktop Understanding types of visualization Loading data from multiple sources Data type and the type of default chart on drag drop Geo location map integration

     Lab: Load the data from excel file and validating the data types and using default charts

```
 sample dashboard with Animation Visual
```
Financial sample data in Power BI Preparing sample dashboard as get started Map visual types and usages in different variation Understanding scatter Plot chart with Play axis and the parameters

     Lab: Variations of Scatter Chart and Map Chart with conditional formatting and using play axis

```
 Power BI Artificial Intelligence Visual
```
Understanding the use of Al in Power BI Alanalysis in Power BI using chart Q & A chat bot and the use in real life Hierarchy tree

     Lab: Create visuals with the help of Al like Q&A smart Narrative

```
 Power BI Visualization
```
Understanding Column Chart Understanding Line Chart Implementation of Conditional formating Implementation of Formatting techniques

     Lab: Bar & Column chart differences and Coloring and formatting

```
 Power Query Editor
```
Loading data from folder Understanding Power Query in detail Promote header, Split to limiter, Add columns, append, merge queries, etc.

     Lab: Formatting the data and removing unwanted fields

```
 Modelling with Power BI
```
Loading multiple data from different format Understanding modelling (How to create relationship) Connection type, Data cardinality, Filter direction Making dashboard using new loaded data

     Lab: Creating relationships with different Cardinality with examples

```
 Modelling with Power BI
```
Loading multiple data from different format Understanding modelling (How to create relationship) Connection type, Data cardinality, Filter direction Making dashboard using new loaded data

     Lab: Creating relationships with different Cardinality with examples

```
 Power Query Editor Filter Data
```
Power Query Custom Column & Conditional Column Manage Parameter Introduction to Filter and types of filter Trend analysis, Future forecast

     Lab: Power Query Custom Column (M-Code) and Manage Parameter

```
 Customize the data in Power BI
```
Drill through function and usage Button triggers Bookmark and different use and implementation Navigation buttons

     Lab: How to create Bookmarks and Navigation buttons

```
 Dax Expressions
```
Introduction to DAX Table Dax, Calculated column, DAX measure and difference Eg: Calendar, Calendar auto, Summarize, Group by, etc Calculated Column Related, Lookup value, switch, Datedif, Rankx, Date functions Dax Measure and Quick Measure Remove filters, Keep filters, All, Allselected, Time Intelligence Functions,Rolling average, YoY, Running total

     Lab: Revision of theTable dax formulas Revision of the Calculated Columns formulas Revision of the Calculated Dax formulas

```
 Custom Visual
```
Custom visual and understanding the use of custom Loading custom visual, Pinning visual Loading to template for future use Publishing Power Bi

     Lab: Creating Visualization and Publishing Power BI Service

```
 Power BI Service
```
Introduction to app.powerbi.com Schedule refresh Data flow and use power bi from online Download data as live in Powerpoint and more

     Lab: Complete Power BI Online Services

```
 Anaconda installation, Introduction to python, Data types, Operators
```
Variables, data types (Integer, Boolean, Float, List, Tuple, String), Operators in Python

```
 Data types Contd, Slicing the data, Inbuilt functions in Python
```
Dictionaries, Sequence methods, Concatenate, Repetition, Len, Min Max Functions, Index Position, Addition and deletion of elements, Reverse, Sorting

```
 Sets, Set Theory, Regular Expressions, Decision making Statements
```
Sets, re module (Findall, Search, Split, Match) IF, elif, getting input from user, identity operator

```
 Loops, Functions, Lambda functions, Modules
```
For, While Loops, Functions, Lambda Functions, Math Module, Calendar Module, Date & Time Module

```
 Pandas, Numpy, Matplotib, Seaborn
```
e Data frame creation, using different methods, Using Pandas analysis on Universities Salary data Sets, Visualization using Matplotlib and Seaborn, Numpy Introduction

What Will You Learn?

Course Curriculum

Data Analytics With Tableau And Power Bi

What Will You Learn?

Course Curriculum

Module 1: Excel

Module 2: MySQL

Module 3: Tableau

Module 3: Power Bl

Module 4: Python Programming