Data Analytics Full Course

• Course Introduction
• Data Analytics in Different Sectors

Calculation and Tables, Home Tab

Page Layout, Review, View

Most frequently used functions

• Spreadsheet analytical techniques: Overview
• The Fill Handle & Autocorrect
• Data Validation
• Conditional Formatting

Circular References: Iterations

• 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
• Curve Fitting: Sigmoid Curves
• 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
• Time Series Analysis and Forecasting
• Marketing strategy analysis: Demand curve & Pricing

• Value at Risk: Non Parametric (Historical) VaR
• Variance-Covariance, Risk modeling
• Probabilities in action: Calculating an Optimal Bid

• Excel exam
• SQL for Data Analytics

• 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

• 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

• 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

• Connect to data sources
• Prepare data for analysis
• Customize fields
• Understand dimensions and measures
• Understand discrete and continuous fields
• Understand aggregations

• 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

Power BI exam

Python basics

• Syntax
• Variables & Data Types (Strings, Numbers)
• Getting Input from Users

• If Statement
• Lists, Tuples, Sets, Dictionaries

• Loops
• Functions, Lambda

• Error handling (Try / Except)

• Built-in packages (random, os, request, math)
• Python for data analysis

• Data frames, reading data from csv, html, json
• Filtering, and sorting
• Adding/removing columns

• Updating Rows and Columns
• Conditional changes
• Join, merge, append

• Indexing
• Aggregation
• Cleaning data
• Casting datatypes
• Working with datetime

• Line plot
• Bar chart
• Pie chart

• Histogram
• Time series
• Subplots

Python exam