Data Analytics Full Course

• Course Introduction

• Calculations in Excel, Mathematical operators

• Relative and absolute references

• Naming cells and selected ranges and using them in formulas

• Clipboard / Types of Paste Special

• Sorting and Filtering Tables: Sort/Filter

• Find and Replace

• Conditional Formatting

• Protection of workbooks, sheets, and cells

• Advanced Filter

• Data Tools-Text to Columns, Flash Fill, Data Validation, Remove Duplicates, Consolidate

• Grouping and Ungrouping data

• Subtotal tool 

• Most frequently used functions-Text Functions, Date and Time Functions, Statistical Functions, Mathematical Functions

•  Assigning practical problems, solution checks 

• Most frequently used functions-Logical Functions, Lookup and Reference Functions, Information Functions

• Using functions in Conditional Formattind and Data Validation

• Assigning practical problems, solution checks

• Smart Tables
• Analysis with Pivot Tables, Pivot Charts

• Circular References: Iterations

• Analytic functions & tools: Autofill, Autocorrect

• Text & Math functions

• Practical problems

• What-If Controls: The Goal Seek Command & Solver: Introduction
• What-If Controls: One & Two-Dimensional Data Tables
• What-If Controls: Using the Scenario Manager for Sensitivity Analysis
• Charts & Graphics: Plotting Data
• Combining Chart Types: Falling ball speed
  

• Measures of Central Tendency, Asymmetry, and Variability
• Correlation & Predictability
• Integration & Differentiation

• Regression and Curve Fitting
• Multiple Regression: Linear Estimates
• Nonlinear Regression, Dealing with Categorical Data
• Confidence Intervals & Hypothesis Testing

• Linear algebra: Reiterations and Matrixes

• Linear algebra: Solving Equations

• Types of Distributions: Sampling distribution

• Sampling Techniques

• Types of Random variables: Discrete & Continuous

• Probability Distributions

• “Frequestist” Probability: Monte Carlo Simulations, Bayesian probability

• Profitability Analysis

• Data modelling

• Scenario analysis

• Time Series Analysis and Forecasting 
• Marketing strategy analysis: Demand curve & Pricing

• Value at Risk: Non Parametric (Historical) VaR
• Variance-Covariance, Risk modeling

• Uncertainty & Risk, Portfolio risk, Quantitative risk management

• Probabilities in action: Calculating an Optimal Bid

• Excel exam 

• What is a Database? Definition, Meaning, Types, Example

• Describing the fundamental building blocks: tables, columns, primary keys, and foreign keys

• Relational Databases and SQL  

Basic Data Types of SQL  

• Numeric  

• Character  

• Boolean  

• Datetime 

• Learn to write common SQL commands including SELECT and FROM

• Retrieving data with SELECT

• Specifying column expressions

• Sorting the result with ORDER BY - DESC ,ASC Query with EXAMPLE

• Handling NULL values in expressions

• Restricting rows with the WHERE filter AND, OR, IN, NOT IN Query

• Testing for equality or inequality

• Applying wildcard characters — Like, NOT Like, Escape, ( % ), ( _ )

• Avoiding NULL value pitfalls - IS NULL , IS NOT NULL

• LIMIT , OFFSET — Learn with Example

• Introduction to single row functions

• Character Functions

• Numeric Functions

• Conversion Functions

• Date Functions

• Conditional formatting with the CASE expression

• Utilizing the CASE expression to simulate IF tests

• • Date Functions

  • Utilizing the CASE expression to simulate IF tests

  Building the Database Schema and SQL Basics

  • Building tables with CREATE TABLE

  • Modifying table structure with ALTER TABLE

  • Adding columns to an existing table

  • Removing tables with DROP TABLE

  • Protecting data integrity with constraints

  • Guaranteeing uniqueness with primary key constraints

  • Enforcing integrity with foreign key constraints

  • Improving performance with indexes

  Manipulating Data

  • Adding table rows with INSERT

  • Changing row content with UPDATE

  • Removing rows with DELETE

  • Controlling transactions with COMMIT and ROLLBACK

• Learn to write INNER JOINs to combine data from multiple tables

• Learn to write LEFT JOINs and RIGHT JOINs to combine data from multiple tables

• Creating a Cartesian product with CROSS JOIN

• Stacking results with UNION

• Identifying matching rows with INTERSECT

• Utilizing MINUS to find non matching rows

• Testing the existence of rows

• Placing subqueries in the column list

• Creating complex expressions containing subqueries

• Selecting data from a query result set

• Using subqueries in the FROM clause

• Summarizing data using SUM, AVG and COUNT

• Finding the highest/lowest values with MAX and MIN

• Defining the summary level with GROUP BY

• Applying filter conditions with HAVING

• Introduction to Analytic functions

• Syntax for Analytic functions

• the Partition Clause

• Order By Clause

• Row number Functions

• Rank Function

• Dense_rank Function

• Lag Function

• ListAgg Function

• Lead Function

• First and Last Functions

• SQL exam 

• Introduction

• Downloading Power BI & adjusting settings

• Exploring the Power BI Desktop interface & workflow

• Helpful Power BI resources

Connecting and Shaping Data with Power BI Desktop

• Connect to data sources

• Types of data connectors in Power BI Desktop

• Change the data source settings

• The Power BI Query editor (aka Power Query)

• Basic table transformations in Power BI

• Working with text tools

• Working with numerical values

• Working with date & time tools

• Shape and transform tables

• User-friendly naming conventions

• Resolving inconsistencies & nulls

• Evaluate and transform column data types

• Append queries

• Merge queries and expand table

• Merge queries with group by, and different types of joins"

• Pivot & Unpivot

• Conditional columns

• Columns from examples

• Transpose

• Data profiling

• Resolve data import errors

• Files from folders

• Data loading with parameters

• What is a “Data Model”?

• Principles of data normalization

• Understanding data tables vs. lookup tables

• Creating table relationships in Power BI Desktop

• Design a data model that uses a “Star” schema

• ""Snowflake"" schemas in Power BI

• Managing & editing table relationships in Power BI Desktop

• Managing active vs. inactive relationships

• Understanding relationship cardinality

• Understanding filter flow

• Two-way filters in Power BI Desktop (USE WITH CAUTION!)

• Hiding fields from the Power BI report view

• Table and column properties

• Common date table (using DAX and Power Query)

• Hierarchies and data granularity

• Role playing dimensions

• Power BI data model best practices

• Intro to Data Analysis Expressions (DAX)

• Intro to DAX calculated columns

• Basic operators

• IF, BLANK and ISBLANK

• AND, OR and NOT

• SWITCH

• Measures -an introduction, with standard aggregations including COUNTBLANK

• Aggregation of calculations

• Other statistical functions

• Rounding functions

• Division functions - MOD and QUOTIENT

• SIGN (and use with SWITCH) and ABS

• COUNT functions (COUNTA, DISTINCTCOUNT, COUNTROWS), exponential function

• Text searching

• Text extraction and substitution

• Text conversion

• Other text functions

• ISERROR and LOOKUPVALUE

• Other information functions

• RELATED - Flatten out a parent-child hierarchy

• RELATEDTABLE and COUNTROWS

• ALL, FILTER, CALCULATE, ALLEXCEPT, ALLSELECTED

• Date and time functions, FIRSTDATE, LASTDATE

• Start of... and End of..., Previous... and Next...

• DATESINPERIOD, DATESMTD, DATESQTD, DATESYTD, TOTALMTD, TOTALQTD, TOTALYTD

• Opening Balance and Closing Balance

• Semi-additive measures

• SAMEPERIODLASTYEAR and PARALLELPERIOD

• Other Time Intelligence Functions

• Quick measures

• Exploring the “Report” view in Power BI Desktop

• Table and matrix

• Column charts, line charts and area charts

• Combo charts and treemaps

• Card, multi-row card, and gauge visual

• Pie chart, donut chart and funnel

• Scatter chart, waterfall chart and maps

• Customize and configure visuals

• Custom tool tips

• Configure report page

• Conditional formatting

• Applying slicing and filtering

• Sorting

• Synch slicers

• Using themes

• Bookmarks

• Page navigation

• Edit interactions

• Analyze feature, Identify outliers

• Create groups, bins and clusters

• Analytics pane, Forecast feature

• Logging into Power BI Service and a quick look around

• Power BI terminology

• Datasets and Reports in Power BI Service

• Publish reports and export data

• Create a workplace

• Manage tiles in a dashboard

• Analyze in Excel feature 

Introduction to Tableau. Understanding Tableau Concepts

• Connect to data sources

• Prepare data for analysis

• Customize fields

• Understand dimensions and measures

• Understand discrete and continuous fields

• Understand aggregations

• Create basic charts

• Create and use filters

• Create parameters to enable interactivity

• Create calculated fields

• Create table calculations

• Structure the data

• Map data geographically

• Create dashboards and stories

• Summarize, model, and customize data by using the Analytics feature

• Power BI exam 

• Intro to programming, Python Interpreters

• Syntax

• Variables & Data Types (Strings, Numbers)

• Strings , Numbers, Booleans

• Getting Input from Users

• Type Conversion

• Tuples

• Lists

• Lists

• ==' vs is

• Copy vs =

• Sets

• If Statement

• Dictionaries

• While loop

• Task scheduling and .py files

• For Loop

• Functions

• Return statement

• Built-in packages (random, os, request, math)

• Error handling (Try / Except)

• Data frames

• Reading df-s from different sources 

• Loc,iloc

• Concat

• Renaming columns

• Filtering, and sorting

• Adding/removing columns

• Conditional changes

• Join, merge, append

• Apply functions

• Replace values

• Working with NA-s

• Casting datatypes

• Time series

• Aggregation

• Line plot

• Bar chart

• Pie chart

• Histogram

• Python exam