Data Analytics

• Introduction to Spreadsheets
• Save Workbooks, Access recent workbooks & Pin workbooks
• Excel Templates
• Tabs, Ribbons & Groups

• Structure of an Excel Function
• SUM() Function
• MIN() & MAX() Functions
• AVERAGE() Function

• Applying Simple Formats
• Conditional Formatting
• Named Ranges and Tables

• Datasets
• The IF() Function
• Nested-IF()
• IFS() Function

• Datasets
• VLOOKUP() Function
• HLOOKUP() Function
• Submit your Assignment

• Datasets
• Basic Excel Charts
• Pivot Tables
• Grouping Pivot Tables

• Datasets
• Dashboard - 1
• Dashboards - 2
• PowerPivot

• Datasets
• What If Analysis - Goal Seek Tool
• What If Analysis - Solver Tool
• What If Analysis - Data Tools

• Datasets
• Data Cleaning in Excel
• Importing Data from External Sources - Web Example

Excel Capstone Project

• Capstone Project
• Submit Project

• Introduction to business intelligence
• Role of a Power BI data analyst
• Power BI Desktop and Power BI Service
• Navigating the Power BI interface

• Connecting to different data sources
• Introduction to Power Query
• Changing data types
• Handling blanks, errors and duplicates

• Understanding fact and dimension tables
• Creating table relationships
• Primary and foreign keys
• Cardinality and filter direction

• Introduction to DAX
• Calculated columns and measures
• SUM, COUNT, AVERAGE and DIVIDE
• Using CALCULATE

• Tables, matrices and charts
• Cards and KPI visuals
• Maps and geographical analysis
• Filters and slicers

• Publishing reports to Power BI Service
• Creating and managing workspaces
• Sharing reports and dashboards
• Configuring scheduled refresh

• What databases are and why they are used
• Tables, records, fields, and data types
• Relational and non-relational databases
• Primary keys and foreign keys

• Entity-relationship diagrams
• Creating tables and relationships
• One-to-one, one-to-many, and many-to-many relationships
• Basic normalization concepts

• Creating databases and tables
• SELECT, FROM, and WHERE
• Sorting with ORDER BY
• Limiting and removing duplicate results

• Aggregate functions
• GROUP BY and HAVING
• Inner, left, and right joins
• Subqueries

• User roles and permissions
• Importing and exporting data
• Backups and recovery concepts
• Indexes and basic query optimization

Design, build, and analyze a small relational database end-to-end

• Design and create a small relational database
• Import and query business data using SQL
• Analyze the results with Python
• Produce a short analytical report

• Introduction to Spreadsheets
• Save Workbooks, Access recent workbooks & Pin workbooks
• Excel Templates
• Tabs, Ribbons & Groups

• Structure of an Excel Function
• SUM() Function
• MIN() & MAX() Functions
• AVERAGE() Function

• Applying Simple Formats
• Conditional Formatting
• Named Ranges and Tables

• Datasets
• The IF() Function
• Nested-IF()
• IFS() Function

• Datasets
• VLOOKUP() Function
• HLOOKUP() Function
• Submit your Assignment

• Datasets
• Basic Excel Charts
• Pivot Tables
• Grouping Pivot Tables

• Datasets
• Dashboard - 1
• Dashboards - 2
• PowerPivot

• Datasets
• What If Analysis - Goal Seek Tool
• What If Analysis - Solver Tool
• What If Analysis - Data Tools

• Datasets
• Data Cleaning in Excel
• Importing Data from External Sources - Web Example

Excel Capstone Project

• Capstone Project
• Submit Project

• Introduction to business intelligence
• Role of a Power BI data analyst
• Power BI Desktop and Power BI Service
• Navigating the Power BI interface

• Connecting to different data sources
• Introduction to Power Query
• Changing data types
• Handling blanks, errors and duplicates

• Understanding fact and dimension tables
• Creating table relationships
• Primary and foreign keys
• Cardinality and filter direction

• Introduction to DAX
• Calculated columns and measures
• SUM, COUNT, AVERAGE and DIVIDE
• Using CALCULATE

• Tables, matrices and charts
• Cards and KPI visuals
• Maps and geographical analysis
• Filters and slicers

• Publishing reports to Power BI Service
• Creating and managing workspaces
• Sharing reports and dashboards
• Configuring scheduled refresh

• Tableau interface
• Connecting Excel/CSV
• Dimensions vs Measures
• Data types

• Bar, line, area & pie charts
• Tables & heatmaps
• Maps & scatter plots
• Marks card

• Dimension & measure filters
• Date filters
• Top N filters
• Parameters

• Calculated fields
• IF/ELSE & CASE
• Date and string calculations
• Aggregations

• Dashboard structure
• KPI cards
• Containers
• Interactive dashboards

Prepare data, define a business problem, and deliver a full BI dashboard with actionable recommendations

• Data preparation
• Business problem definition
• Exploratory analysis
• KPI development