What Is Data Analysis?
• A Set of Process performed on row data that enables
us extract useful insights that helps decision
makers to understand the Past , expect the future and
take correct decisions
Why Is Data Analysis Important For Business?
Take a decision for
Improve my
Solve Problem Avoid Problem Performance
What are Business Goals?
Increased profitability
Employees
Growth Sales Development
Which
Data?
Data that contain The Answers of
Business Questions
Data analysis Process
Cleaning & Preparing
• Handling missing data
• Removal of unwanted
observations
• Fixing Structure errors
Collecting & Exploring • Managing Unwanted Outliers Analysis
• Collect what I need • Reshape 3 Data • Descriptive analytics
• Exploring Missing Data • Diagnostic analytics
• Exploring outliers
2 4 • Predictive analytics
• Exploring illogical data • Prescriptive analytics
• Exploring Errors
1 5 Visualization
Ask Questions . Storytelling
• Know your audience • Grape attention
• Your Knowledge about • Choice the simple ways
Business
• Your Experience
www.presentationgo.com
What should I
Explore?
• Missing Data
• Outliers
• Contaminated Data
• Inconsistent Data
• Invalid Data
• Duplicate Data
• Data Type Issues
• Structural errors
How is Data analyst?
Curious Analytical thinking Solve Problem
Data analyst Skills
General Technical Skills Domain knowledge
(Language, Communication, Search, (Data cleaning and preparation, Data
Dealing with Emails, ……) analysis and exploration, data
visualizations, Statistical knowledge,
Data Modeling & Calculations, and
Tools )
What areas does a data analyst work in?
Sales Marketing Supply Chain HR Finance Customer
Service
Sports Other….
Course Content
• Excel Basics
• Data Analysis using Excel
• Data Analysis using Power Bi
• Data Analysis using SQL
• Data Analysis Using Tableau (Advanced Point)
• Data Analysis Using Looker (Advanced Point)
Excel Basics
• Interface
• Data types
• Insert Data
• Insert columns &Rows
• Simple Calculations
• Autofill
• Format Data
• Copy & Paste
• Selection
• Reference cells
• Freeze Pane
• Text to Column
• Concatenate Values
• Link Between sheets
Excel Basics(2)
• Remove Duplicates
• Find & Select
• Comments
• Create Table
• Named Table
• Format Table
• Convert to Range
• Slicers
• All Benefits of table
• Make Sort & Filter for (Texts, Dates and Numbers)
• Advanced Filter & Sort
• Conditional Formatting (Mange rules, Clear rules, Data bars, Color Scale, Icons, and Format by group of
Condition)
• Other…….
Excel Basics (Functions)
• Text Functions
• For Examples (Trim, Proper, Upper, Lower, Concatenate, Left, Mid, Right, ……….)
• Lookup Functions
• For Examples (V lookup, H lookup, Index, Match, …………)
• Logical Functions
• For Examples (If, Nested if, And, Or, If error, ……)
• Statistic Functions
• For Examples (Sumif, Sumifs, Countif, Countifs, Maxif, Minif, Averageif, Count, Counta,
….)
• Date Functions
• For Examples ( Year, Month, Day, Hour, Minute, Second, datediff,
NETWORKDAYS.INTL, Date,……)
Data Analysis using Excel (PIVOT TABLES)
• Preparing Data to Create Pivot Tables
• Create, design, change layout and Refresh pivot table
• Grouping Data (dates, text, and numbers)
• GEVERATE GET PIVOT DATA
• Double click, Filter, Sort and search & sort filed list
• Auto fit Column Width, Show & Hide Labels and Custom Style
• Conditional Format in pivot table
• Paste Link & Distinct count
• Calculated Fields and Items
• Show Value as
• Pivot Chart, Format charts, Slicer and Timeline
• Advanced Tricks
Data Analysis using Excel (POWER QUERY)
• Extract data from different Sources
• For Example (Excel, Text File, Csv, Access, Web, From Folder, Sql Server, ….)
• Home Tab
• For Example (Choice & Remove Columns, Keep & Remove Rows, Split column &
Grouping By, Transform (Change type & Replace Value), Merge & Append Queries,
Sort & Change Data Source, …………….)
• Transform Tab
• For Example (Transpose, Pivot & Unpivot Columns, Fill & Count Rows, Detect Data
Type, Use First row as header, Convert to list & move, ………..)
• Add Column Tab
• For Example (Custom From Example, Custom Column, Index Column, Merge Columns
& Extract, Standard, Date,………..)
• View Tab
• For Example (Show & Hide Applied Steps, Show & Hide Formula bar, Column Quality
Data Analysis using Excel (POWER PIVOT)
Create Model and Intro Transform from Working With DAX Create New Columns Create KPIS
to Relationships Normalize to DE with DAX
normalize
One to One Star Schema , Snowflake Schema
Cover more than 70
One to Many Special Cases
Many to Many
functions
Learn how to work
with DAX and make
it easy
Data Analysis using Excel (Charts&
Dashboards)
Optimal Use of each How to Format the Learn Data Visualization Create Dynamic
Chart ( why & When ) chart like professional as a Science Dashboards Using Excel
( tell Your Story, Colors,
Keep it Semple, Say it With
Picture)
Data Analysis using Power BI (Intro)
• Introduction & Interface
• Get Data
• Working With Charts
• Filter Pane
• Conditional Format
• Hierarchies
• Bookmarks, Page tooltip and Drill through
• Working with all Tabs
• For Example (Home Tab, Insert Tab, Modeling Tab, View Tab, Format,
Data / Drill,…..)
Data Analysis using
Power BI (POWER QUERY)
• Extract data from different Sources
• For Example (Excel, Text File, Csv, Access, Web, From Folder,
Sql Server, ….)
• Home Tab
• For Example (Choice & Remove Columns, Keep & Remove
Rows, Split column & Grouping By, Transform (Change type
& Replace Value), Merge & Append Queries, Sort & Change
Data Source, …………….)
• Transform Tab
• For Example (Transpose, Pivot & Unpivot Columns, Fill &
Count Rows, Detect Data Type, Use First row as header,
Convert to list & move, ………..)
• Add Column Tab
• For Example (Custom From Example, Custom Column, Index
Column, Merge Columns & Extract, Standard, Date,………..)
• View Tab
• For Example (Show & Hide Applied Steps, Show & Hide
Formula bar, Column Quality & Column Profile,…..)
Data Analysis using
Power BI (POWER
PIVOT)
• Create Model and Intro to Relationships
• One to One
• One to Many
• Many to Many
• Transform from Normalize to DE normalize
• Star Schema , Snowflake Schema
• Special Cases
• Working With DAX
• Cover more than 70 functions
• Learn how to work with DAX and make it easy
• Create New Columns with DAX
• Create KPIS
SQL for Data
Analyst
• Intro to Database
• Working with DDL
• (create, Drop, Alter, and Truncate)
• Working with DML
• (insert, Update, and Delete)
• Working with DQL
• ( select, ORDER BY, DISTINCT, WHERE, AND,
OR, IN, BETWEEN, LIKE,)
• Joining tables (INNER JOIN, LEFT JOIN ,
RIGHT JOIN, FULL OUTER JOIN, and
CROSS JOIN)
• Grouping data (GROUP BY, and HAVING)
SQL for Data
Analyst(2)
• Subquery
• Pivot
• Functions
• (String functions, Date
Functions, and Numirc)
• Window Functions
• Case When
• Stored Procedures
• Views
Power Query
• What is the Power Query?
• When Power Query?
• What are Power Query advantages?
Power Query
• Power Query is a data
transformation and data
preparation engine.
• ETL TOOL Inside Excel and
Power BI
When Power Query?
• Data Preparing & Cleaning
What is ETL?
• E:- Extract Data from Sources
• T:- Transformation Data
• L:- Loading Clean Data to Data
Wearhouse
What Are ETL Tools?
• Azure Data Factory (M.S)
• Databricks (M.S)
• SSIS (M.S)
• Power Query (M.S)
• Datastage (IBM)
• AWS Databricks (Amazon)
• Talend
• Oracle Data Integrator
• Jupyter
Where do Companies Store
data?
• Databases
• Files
• Platforms
What are Power Query
advantages?
• Automation without Code
• Solve Normal Excel Limitation
• Working with Complex data and make
data cleaning by simple ways
• Get data from most Sources
