Data Analysts Toolbox: Excel, Python, Power Bi, Pivottables

Learn Advanced Pivot Tables, Power Query, Power Pivot, Power BI, and Python in this four-course bundle

What you'll learn
How to create amazing looking dashboards using Pivot Tables
Advanced data analysis techniques
How to do a PivotTable (a quick refresher)
How to format a PivotTable, including adjusting styles
Advanced Sorting and Filtering in PivotTables
How to use 3D Maps from a PivotTable
How to update your data in a PivotTable and Pivot Chart
Advanced Sorting and Filtering in PivotTables
Analyze huge buckets of data to make informed business decisions
Become confident cleaning, sorting and linking data from various sources
How to create stunning, interactive dashboards with Power BI
How to share your analysis and dashboards using Power BI Online
To import CSV and Excel files into Power BI Desktop
All about DAX including using the COUTROWS, CALCULATE, and SAMEPERIODLASTYEAR functions
All about using the card visual to create summary information
How to create amazing visuals, such as clustered column charts, maps, and trend graphs
How to use Slicers to filter your reports
How to edit the interactions between your visualizations and filter at visualization, page, and report level
Put their skills into practice with a real Python project
What is Python and why was it created
How Python fits into the diverse ecosystem of programming languages.
The basic data types in Python - Strings, Integers, Floats, and Boolean
All about Pythons built-in functions
How to debug errors in Python
How Variables and Functions work in Python
How to use IF-Else Statements in Python
All about storing complex data, including Lists and Dictionaries
How to install Python locally
How to write your first script in Python
Requirements
You'll need a copy of Microsoft Excel that is compatible with the Power Pivot tool.
A good understanding of MS Excel. This is an advanced level course.
Power BI Desktop installed on your machine is required to take the practice exercises
A good knowledge of MS Excel is advised but not necessary
Description
In business, being able to understand, harness, and use data is no longer a skill reserved for a handful of well-paid analysts. It's becoming an essential part of many roles.If that sounds daunting, don't worry. There is a growing set of tools designed to make data analysis accessible to everyone, in this huge-value, four-course Data Analysts Toolbox bundle we look in detail at three of those tools: Excel, Python, and Power BI. In isolation Excel, Python, and Power BI are useful and powerful. Learn all three and you are well on your way to gaining a much deeper understanding of how to perform complex data analysis.This Data Analysts Toolbox bundle is aimed at intermediate Excel users who are new to Python and Power BI. All courses include practice exercises so you can put into practice exactly what you learn.Here's what each course covers:Introduction to PythonThe basic data types in Python - Strings, Integers, Floats, and BooleanAll about Pythons built-in functionsHow Variables and Functions work in PythonHow to debug errors in PythonAll about Python KeywordsHow to use IF-Else Statements in PythonAll about storing complex data, including Lists and DictionariesAll about Python Modules and how to install themHow to install Python locallyHow to write your first script in PythonTo complete your first Python projectAdvanced PivotTablesHow to do a PivotTable (a quick refresher)How to combine data from multiple worksheets for a PivotTableGrouping, ungrouping and dealing with errorsHow to format a PivotTable, including adjusting stylesHow to use the Value Field SettingsAdvanced Sorting and Filtering in PivotTablesHow to use Slicers, Timelines on multiple tablesHow to create a Calculated FieldAll about GETPIVOTDATAHow to create a Pivot Chart and add sparklines and slicersHow to use 3D Maps from a PivotTableHow to update your data in a PivotTable and Pivot ChartAll about Conditional Formatting in a PivotTableHow to create amazing looking dashboardsPower Pivot, Power Query and DAXHow to get started with Power QueryHow to connect Excel to multiple workbooksHow to get data from the web and other sourcesHow to merge and append queries using Power QueryHow the Power Pivot window worksHow to set up and manage relationships in a data modelHow to create a PivotTable to display your data from the Power Pivot data modelHow to add calculated columns using DAXHow to use functions such as CALCULATE, DIVIDE, DATESYTD in DAXAll about creating Pivot Charts and PivotTables and using your data modelHow to use slicers to adjust the data you displayPower BIWhat is Power BI and why you should be using it.To import CSV and Excel files into Power BI Desktop.How to use Merge Queries to fetch data from other queries.How to create relationships between the different tables of the data model.All about DAX including using the COUTROWS, CALCULATE, and SAMEPERIODLASTYEAR functions.All about using the card visual to create summary information.How to use other visuals such as clustered column charts, maps, and trend graphs.How to use Slicers to filter your reports.How to use themes to format your reports quickly and consistently.How to edit the interactions between your visualizations and filter at visualization, page, and report level.***Exercise and demo files and project files included***This mega-value bundle includes:21+ hours of video tutorials200+ individual video lecturesExercise files to practice what you learnedCertificate of completionHere’s what our students are saying…"The instructors have explained everything amazing and the resources are super helpful."- Oscar"This course is very relevant in my line of work. The facilitators are detailed in their explanation & don't rush through the information."- Thulani"My name is Kenvis and I have a knowledge in IT but never thought of specializing in Data Analytics before. This was so exciting and I am now anxious to learn more."- Kenvis"Thank you for such a wonderful teaching. This is a great learning experience for me."- Ankush

Overview

Section 1: Advanced PivotTables: Introduction

Lecture 1 Introduction to Advanced PivotTables

Lecture 2 WATCH ME: Essential Information for a Successful Training Experience

Lecture 3 DOWNLOAD ME: Course Exercise Files

Lecture 4 DOWNLOAD ME: Course Support Files

Lecture 5 PivotTables Recap

Section 2: Advanced PivotTable: Importing Data

Lecture 6 Importing data from a text file

Lecture 7 Importing data from Access

Lecture 8 Exercise 01

Section 3: Advanced PivotTable: Preparing Data for Analysis

Lecture 9 Cleaning Data

Lecture 10 Tabular Data

Lecture 11 Exercise 02

Section 4: Advanced PivotTable: Creating and Manipulating PivotTables

Lecture 12 Creating and Manipulating a PivotTable

Lecture 13 Combining Data from Multiple Worksheets

Lecture 14 Grouping and Ungrouping

Lecture 15 Report Layouts

Lecture 16 Formatting Error Values and Empty Cells

Lecture 17 Exercise 03

Section 5: Advanced PivotTables: Formatting PivotTables

Lecture 18 PivotTable Styles

Lecture 19 Custom Number Formatting

Lecture 20 Exercise 04

Section 6: Advanced PivotTables: Value Field Settings

Lecture 21 Summarizing Values

Lecture 22 Show Values As

Lecture 23 Exercise 05

Section 7: Advanced PivotTables: Sorting and Filtering

Lecture 24 Advanced Sorting

Lecture 25 Advanced Filtering

Lecture 26 Exercise 06

Section 8: Advanced PivotTables: Interacting with PivotTables

Lecture 27 Inserting and formatting Slicers

Lecture 28 Inserting and formatting Timelines

Lecture 29 Connecting Slicers to multiple PivotTables

Lecture 30 Using Slicers in Protected Workbooks

Lecture 31 Exercise 07

Section 9: Advanced PivotTables: Calculations

Lecture 32 Creating a Calculated Field

Lecture 33 Creating a Calculated Item

Lecture 34 Solve Order and List Formulas

Lecture 35 GETPIVOTDATA

Lecture 36 Exercise 08

Section 10: Advanced PivotTables: Pivot Charts

Lecture 37 Creating a Pivot Chart

Lecture 38 Formatting a Pivot Chart - Part 1

Lecture 39 Formatting a Pivot Chart - Part 2

Lecture 40 Creating a Map Chart using Pivot Data

Lecture 41 Dynamic Chart Titles

Lecture 42 Include a Sparkline with your PivotTable

Lecture 43 Exercise 09

Section 11: Advanced PivotTables: Conditional Formatting

Lecture 44 Highlighting Cell Rules

Lecture 45 Graphical Conditional Formats

Lecture 46 Conditional Formatting and Slicers

Lecture 47 Exercise 10

Section 12: Advanced PivotTables: Dashboards

Lecture 48 Creating an Interactive Dashboard - Part 1

Lecture 49 Creating an Interactive Dashboard - Part 2

Lecture 50 Updating Pivot Charts and PivotTables

Lecture 51 Exercise 11

Section 13: Course Close

Lecture 52 Course Close

Section 14: Introduction to PowerPivot and PowerQuery

Lecture 53 Welcome and Overview

Lecture 54 WATCH ME: Essential Information for a Successful Training Experience

Lecture 55 DOWNLOAD ME: Course Exercise Files

Lecture 56 DOWNLOAD ME: Course Demo Files

Lecture 57 What is Power Query?

Lecture 58 What is Power Pivot?

Section 15: Getting Started with Power Query

Lecture 59 Exploring the Power Query Editor

Lecture 60 Common Power Query Transformations

Lecture 61 Editing an Existing Query

Lecture 62 Import Multiple Files from a Folder

Lecture 63 Connect to Data in Another Excel Workbook

Lecture 64 IMPORTANT: Checking the Location of your Query's Source

Lecture 65 Get Data From the Web

Lecture 66 Practise Exercise

Section 16: Useful Power Query Features

Lecture 67 Unpivoting Columns

Lecture 68 Combine Data from Multiple Tables with Merge Queries

Lecture 69 Use Merge Queries to Compare Two Tables

Lecture 70 Stack Data into One Table with Append Queries

Lecture 71 Duplicating and Referencing Queries

Lecture 72 Grouping and Aggregating Data

Lecture 73 Conditional Columns in Power Query

Lecture 74 Practise Exercise

Section 17: Creating the Data Model

Lecture 75 Enable the Power Pivot Add-In

Lecture 76 Understanding the Power Pivot Window

Lecture 77 Creating Relationships Between Tables

Lecture 78 Managing the Relationships of the Model

Lecture 79 Creating a PivotTable from the Data Model

Lecture 80 Hide Fields from Client Tools

Lecture 81 Grouping Queries

Lecture 82 Practise Exercise

Section 18: Introduction to DAX

Lecture 83 Why use DAX?

Lecture 84 Creating Calculated Columns with DAX

Lecture 85 Creating your First DAX Measure

Lecture 86 The COUNTROWS Function

Lecture 87 SUMX and RELATED Functions

Lecture 88 Practise Exercise

Section 19: More DAX Measures

Lecture 89 Create a Date Table in Power Pivot

Lecture 90 The CALCULATE Function

Lecture 91 The DIVIDE Function

Lecture 92 Using the DATESYTD Function

Lecture 93 Calculate the Percentage of a Total

Lecture 94 Practise Exercise

Section 20: Using PivotTables and Slicers

Lecture 95 Create PivotTables and PivotCharts

Lecture 96 Using Slicers with your PivotTables

Lecture 97 Create a Top 10 PivotTable

Lecture 98 Practise Exercise

Section 21: PowerPivot, PowerQuery and Dax: Closing

Lecture 99 Wrap Up

Section 22: Introduction to Power BI

Lecture 100 Welcome and Overview

Lecture 101 WATCH ME: Essential Information for a Successful Training Experience

Lecture 102 DOWNLOAD ME: Course Exercise File

Lecture 103 Downloadable Course Transcript

Lecture 104 What is Power BI?

Lecture 105 Install Power BI Desktop

Lecture 106 A Tour of BI Desktop

Lecture 107 Explore Commonly Used Power BI Options

Section 23: PowerBI: Getting and Transforming Data

Lecture 108 Import Files from a Folder into Power BI Desktop

Lecture 109 Get Data from Excel and Text Files

Lecture 110 Changing Query Course

Lecture 111 Reference Queries to Create Additional Lookup Tables

Lecture 112 Merge Queries in Power Query

Lecture 113 Prevent Queries from Loading into Power BI Desktop

Lecture 114 Practise Exercise

Section 24: Power BI: Data Modelling

Lecture 115 Create Relationships Between Tables

Lecture 116 Create a Dynamic List of Dates

Lecture 117 Create Additional Date Columns for Analysis

Lecture 118 Sort the Month and Weekday Names Correctly

Lecture 119 Mark the Table as a Date Table

Lecture 120 Hide Unnecessary Fields from Report View

Lecture 121 Practise Exercise

Section 25: Introduction to DAX Measures

Lecture 122 Calculate the Total Revenue

Lecture 123 Count the Total Rows of a Table

Lecture 124 Use the CALCULATE Function

Lecture 125 Calculate the Total Revenue Last Year

Lecture 126 Difference Compared to Last Year

Lecture 127 Practise Exercise

Section 26: Power BI: Adding Visualizations to your Report

Lecture 128 Show Summary Information with Cards

Lecture 129 Columns Charts to Compare Values

Lecture 130 Map Visual to Plot Geographic Data

Lecture 131 Slicers to Filter your Reports

Lecture 132 KPI Card to Measure Performance Against a Goal

Lecture 133 Line Graphs to Visualise Trend

Lecture 134 Show Detail with the Matrix

Lecture 135 Top N Lists with the Table Visualisation

Lecture 136 Practise Exercise

Section 27: Power BI: Report Design

Lecture 137 Adding Text Boxes and Shapes

Lecture 138 Use Themes

Lecture 139 Conditional Formatting

Lecture 140 Practise Exercise

Section 28: Power BI: Editing Interactions and Filters

Lecture 141 Edit the Interactions between your Visualisations

Lecture 142 The Filter Pane to Filter at Any Level

Lecture 143 Drill Through to More Detail

Lecture 144 Practise Exercise

Section 29: Power BI Service

Lecture 145 Publish Your Report to the Power BI Service

Lecture 146 Different Ways to Share your Power BI Report

Lecture 147 Practise Exercise

Section 30: Closing

Lecture 148 Wrap Up

Section 31: Python: The Workplace Tech Divide

Lecture 149 Which Side of the Divide Are You On?

Lecture 150 WATCH ME: Essential Information for a Successful Training Experience

Lecture 151 DOWNLOAD ME: Course Exercise Files

Lecture 152 DOWNLOAD ME: Course Project Files

Lecture 153 Beginners Are Welcome

Lecture 154 Course Overview

Section 32: Introduction to Python

Lecture 155 What is Python?

Lecture 156 Python's Comparison to Other Programming Languages

Lecture 157 Examples of Python in the Workplace

Lecture 158 The Easiest Place to Practice Python

Lecture 159 Create an Account Online

Section 33: Python: Basic Data Types

Lecture 160 Python Data Types

Lecture 161 Strings

Lecture 162 Integers

Lecture 163 Floats

Lecture 164 Boolean

Lecture 165 Data Types Exercise

Section 34: Python Built-In Functions

Lecture 166 What is a Built-In Function?

Lecture 167 Where to Look For Built-In Functions

Lecture 168 Most Common Built In Functions

Lecture 169 Built In Functions Exercise

Section 35: Variables and Functions

Lecture 170 Variables and Functions

Lecture 171 Storing Values as Variables

Lecture 172 Comparing Variables with Operators

Lecture 173 Basic Expressions

Lecture 174 Functions

Lecture 175 Commenting

Lecture 176 Variables and Functions Exercise

Section 36: Errors and Debugging

Lecture 177 What is an Error?

Lecture 178 Reading a Stack Trace

Lecture 179 Print Function

Lecture 180 Try and Except

Lecture 181 You Are Not Alone

Lecture 182 Errors Exercise

Section 37: Python Keywords

Lecture 183 Python Keywords

Lecture 184 Common Keywords

Lecture 185 Global

Lecture 186 Keywords Exercise

Section 38: If-Else Statements

Lecture 187 Basic Logic

Lecture 188 Syntax and Inline Evaluation

Lecture 189 Value Evaluation

Lecture 190 Complex If-Else Statements

Lecture 191 If-Else Exercises

Section 39: Storing Complex Data

Lecture 192 Advanced Data Types

Lecture 193 Lists

Lecture 194 Dictionaries

Lecture 195 Looping: Lists

Lecture 196 Looping: Dictionaries

Lecture 197 Advanced Data Exercise

Section 40: Python Modules

Lecture 198 Python Modules

Lecture 199 Python Built In Modules

Lecture 200 Importing Modules

Section 41: Installing Python and Modules

Lecture 201 Python Environments

Lecture 202 Mac

Lecture 203 Windows

Lecture 204 IDEs

Lecture 205 Python Idle

Lecture 206 Managing files and folders

Lecture 207 Executing Scripts

Lecture 208 PIP

Section 42: Project: Automate Data Updates For a Spreadsheet

Lecture 209 Project Introduction

Lecture 210 Setting Up Project

Lecture 211 Reading and Writing to Excel Files

Lecture 212 Working with CSV Files

Lecture 213 Dynamic File Paths

Lecture 214 Transform and Validate Transactions

Lecture 215 Transfer and Save Transactions

Lecture 216 Clean Up the Code

Lecture 217 Hardening the Script

Section 43: Course Close

Lecture 218 What's Next?

Section 44: Introduction to Alteryx for Beginners

Lecture 219 Introduction

Lecture 220 WATCH ME: Essential Information for a Successful Training Experience

Lecture 221 Course Instructor Files

Lecture 222 Alteryx Essentials

Lecture 223 Data Types 101

Lecture 224 Getting Started with the Alteryx Designer

Lecture 225 Building a Workflow in Designer

Lecture 226 The Favorites Tools

Section 45: Alteryx: Data Blend & Prep

Lecture 227 Data filtering for Beginners

Lecture 228 Intro to Alteryx for Excel Users

Lecture 229 Alteryx for the SQL Analyst

Lecture 230 Introduction to Basic Functions - Part 1

Lecture 231 Introduction to Basic Functions - Part 2

Lecture 232 Introduction to Basic Functions - Part 3

Section 46: Alteryx: Data Parsing

Lecture 233 Basic Parsing Methods

Lecture 234 Basic Parsing Methods w/ Dynamic Renaming

Lecture 235 Basic Vlookup and Append

Lecture 236 Working on Multiple Fields in Alteryx

Lecture 237 Build an Alteryx Workflow

Lecture 238 Basic Tips and Tricks

Section 47: Reporting

Lecture 239 Alteryx Best Practices - Visualizing Data

Lecture 240 Alteryx Best Practices - Visualizing Data with Texts and Charts

Lecture 241 Alteryx Best Practices - Layouts and Rendering

Section 48: Alteryx: Analytic Apps

Lecture 242 Introduction to Analytic Apps

Lecture 243 Introduction to Macros

Section 49: Alteryx: Spatial Data Analysis

Lecture 244 Intro to Data Analysis - Working Spatial Data

Lecture 245 Intro to Data Analysis - Measuring

Lecture 246 Intro to Data Analysis - Spatial Objects

Section 50: Alteryx: Analytics

Lecture 247 Introduction to Analytics

Lecture 248 Introduction to K-Centroid Clustering

Lecture 249 Introduction to K-Nearest Neighbor

Lecture 250 Introduction to Market Basket Analysis

Lecture 251 Introduction to Logistic Regression Analysis

Lecture 252 Introduction to Linear Regression Analysis

Lecture 253 Introduction to Tree-based Models Part 1

Lecture 254 Introduction to Tree-based Models Part 2

Section 51: Course Close

Lecture 255 Course Close

Advanced Excel Users,People looking to learn the Python Programming Language