Data Analysts Toolbox Online Course

Data Analysts Toolbox: Excel, Python, Power BI

Being able to understand, harness, and use data is no longer a skill reserved for a handful of well-paid data analysts. It's becoming an essential part of many roles. Learning data analysis can sound daunting, but don’t worry. This video course unboxes the data analyst toolbox bundle, enabling you to learn the tools needed for data analysis.

The course starts by taking you through the topics of advanced pivot tables. You will learn how to create and manipulate pivot tables, import data from Access and Excel into the tables, prepare data for analysis, sort and filter the data, create an interactive dashboard, and a lot more. Next, you will get grips with Power Pivot, Power Query, and Data Analysis Expressions (DAX) and discover how to use Power BI to create striking data visualization. Towards the end, you will learn the Python programming concepts that will help you to write error-free Python scripts for automatically updating data in a spreadsheet.

By the end of this course, you will be able to confidently analyze and visualize huge sets of data using Python, Power Query, Power Pivot, and Power BI.

Course Curriculum

Advanced Pivot Tables: Introduction

• Introduction to Advanced Pivot Tables
• Pivot Tables Recap

Advanced Pivot Tables: Importing Data

• Importing Data from a Text File
• Importing Data from Access
• Exercise

Advanced Pivot Tables: Preparing Data for Analysis

• Cleaning Data
• Tabular Data
• Exercise

Advanced Pivot Tables: Creating and Manipulating Pivot Tables

• Creating and Manipulating a Pivot Table
• Combining Data from Multiple Worksheets
• Grouping and Ungrouping
• Report Layouts
• Formatting the Error Values and Empty Cells
• Exercise

Advanced Pivot Tables: Formatting a Pivot Table

• Pivot Table Styles
• Custom Number Formatting
• Exercise

Advanced Pivot Tables: Value Field Settings

• Summarizing Values
• Show Values As
• Exercise

Advanced Pivot Tables: Sorting and Filtering

• Advanced Sorting
• Advanced Filtering
• Exercise

Advanced Pivot Tables: Interacting with a Pivot Table

• Inserting and Formatting Slicers
• Inserting and Formatting Timelines
• Connecting Slicers to Multiple Pivot Tables
• Using Slicers in a Protected Workbook
• Exercise

Advanced Pivot Tables: Calculations

• Creating a Calculated Field
• Creating a Calculated Item
• Solve Order and List Formulas
• GETPIVOTDATA
• Exercise

Advanced Pivot Tables: Pivot Charts

• Creating a Pivot Chart
• Formatting a Pivot Chart - Part 1
• Formatting a Pivot Chart - Part 2
• Creating a Map Chart Using Pivot Data
• Creating a Dynamic Chart Title
• Include a Sparkline with a Pivot Table
• Exercise

Advanced Pivot Tables: Conditional Formatting

• Highlighting Cell Rules
• Graphical Conditional Formats
• Conditional Formatting and Slicers
• Exercise

Advanced Pivot Tables: Dashboards

• Creating an Interactive Dashboard - Part 1
• Creating an Interactive Dashboard - Part 2
• Updating Pivot Charts and PivotTables
• Exercise

Advanced Pivot Tables: Summary

• Summary

Introduction to Power Pivot and Power Query

• Welcome and Overview
• What is Power Query?
• What is Power Pivot?

Getting Started with Power Query

• Exploring the Power Query Editor
• Common Power Query Transformations
• Editing an Existing Query
• Importing Multiple Files from a Folder
• Connecting to Data in another Excel Workbook
• Important: Checking the Location of Your Query's Source
• Retrieving Data from the Web
• Practice Exercise

Useful Power Query Features

• Unpivoting Columns
• Combining Data from Multiple Tables with Merge Queries
• Using Merge Queries to Compare Two Tables
• Stacking Data into One Table with Append Queries
• Duplicating and Referencing Queries
• Grouping and Aggregating Data
• Adding Conditional Columns in Power Query
• Practice Exercise

Creating a Data Model

• Enabling the Power Pivot Add-in
• Understanding the Power Pivot Window
• Creating Relationships between Tables
• Managing Relationships of the Model
• Creating a Pivot Table from the Data Model
• Hiding Fields from the Client Tools
• Grouping Queries
• Practice Exercise

Introduction to Data Analysis Expressions (DAX)

• Why use Data Analysis Expressions (DAX)?
• Creating Calculated Columns with Data Analysis Expressions (DAX)
• Creating the First Data Analysis Expressions (DAX) Measure
• Using the COUNTROWS Function
• Using the SUMX and RELATED Functions
• Practice Exercise

More Data Analysis Expressions (DAX) Measures

• Creating a Date Table in Power Pivot
• Using the CALCULATE Function
• Using the DIVIDE Function
• Using the DATESYTD Function
• Calculating the Percentage of a Total
• Practice Exercise

Using Pivot Tables and Slicers

• Creating Pivot Tables and Pivot Charts
• Using Slicers with Pivot Tables
• Creating a Top 10 Pivot Table
• Practice Exercise

Power Pivot, Power Query, and Data Analysis Expressions (DAX): Summary

• Power Pivot, Power Query, and Data Analysis Expressions (DAX): Summary

Introduction to Power BI

• Welcome and Overview
• What is Power BI?
• Installing Power BI Desktop
• Tour of Power BI Desktop
• Exploring the Commonly Used Power BI Options

Power BI: Getting and Transforming Data

• Importing Files from a Folder into Power BI Desktop
• Getting Data from Excel and Text Files
• Referencing Queries to Create Additional Lookup Tables
• Merging Queries in Power Query
• Preventing Queries from Loading into Power BI Desktop
• Practice Exercise

Power BI: Data Modelling

• Creating a Relationship between Tables
• Creating a Dynamic List of Dates
• Creating Additional Date Columns for Analysis
• Sorting the Month and Weekday Names Correctly
• Marking the Table as a Date Table
• Hiding Unnecessary Fields from the Report View
• Practice Exercise

Introduction to Data Analysis Expressions (DAX) Measures

• Calculating Total Revenue
• Counting the Total Rows of a Table
• Using the CALCULATE DAX Function
• Calculating the Total Revenue for Last Year
• Difference Compared to Last Year
• Practice Exercise

Power BI: Adding Visualizations to Your Report

• Showing Summary Information with Cards
• Comparing Values with Columns Charts
• Mapping Visual to Plot Geographic Data
• Filtering Reports with Slicers
• Key Performance Indicator (KPI) Card to Measure Performance against a Goal
• Line Graphs to Visualize a Trend
• Showing Details with the Matrix
• Top N Lists with Table Visualization
• Practice Exercise

Power BI: Report Design

• Adding Text Boxes and Shapes
• Using Themes
• Conditional Formatting
• Practice Exercise

Power BI: Editing Interactions and Filters

• Editing Interactions between Visualizations
• Filter Pane to Filter at any Level
• Drilling through to More Detail
• Practice Exercise

Power BI Service

• Publishing a Report to the Power BI Service
• Different Ways to Share a Power BI Report
• Practice Exercise

Power BI: Summary

• Summary

Python: The Workplace Tech Divide

• Which Side of the Divide are You on?
• Beginners Are Welcome
• Course Overview

Introduction to Python

• What is Python?
• Python's Comparison to Other Programming Languages
• Examples of Python in the Workplace
• The Easiest Place to Practice Python
• Creating an Account Online

Basic Data Types

• Python Data Types
• Strings
• Integers
• Floats
• Boolean
• Data Types Exercise

Python Built-in Functions

• What are Built-in Functions?
• Where to Look for the Built-in Functions?
• Most Common Built-in Functions
• Built-in Functions Exercise

Variables and Functions

• Variables and Functions
• Storing Values as Variables
• Comparing Variables with Operators
• Basic Expressions
• Functions

Errors and Debugging

• What is an Error?
• Reading a Stack Trace
• Print Function
• Try and Except
• You Are Not Alone
• Errors Exercise

Python Keywords

• Python Keywords
• Common Keywords
• Global Keywords
• Keywords Exercise

If-Else Statements

• Basic Logic
• Syntax and Inline Evaluation
• Value Evaluation
• Complex If-else Statements
• If-else Exercises

Storing Complex Data

• Advanced Data Types
• Lists
• Dictionaries
• Looping: Lists
• Looping: Dictionaries
• Advanced Data Exercise

Python Modules

• Python Modules
• Python Built-in Modules
• Importing Modules

Installing Python and Modules

• Python Environments
• Installing Python on Mac
• Installing Python on Windows
• Installing Python on Integrated Development Environments (IDEs)
• Installing Python on Integrated Development and Learning Environment (IDLE)
• Managing Files and Folders
• Executing Scripts
• Pip

Project: Automating Data Updates in a Spreadsheet

• Project Introduction
• Setting up the Project
• Reading and Writing to Excel Files
• Working with Comma-Separated Values (CSV) Files
• Dynamic File Paths
• Transforming and Validating Transactions
• Transferring and Saving Transactions
• Cleaning up the Code
• Hardening the Script

Summary

• What's Next?