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

  • 0.0 (0 Reviews)
  • 0 Students
  • 296 Lessons
  • Simon Sez IT

Course Overview

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 Python

  • The basic data types in Python - Strings, Integers, Floats, and Boolean

  • All about Pythons built-in functions

  • How Variables and Functions work in Python

  • How to debug errors in Python

  • All about Python Keywords

  • How to use IF-Else Statements in Python

  • All about storing complex data, including Lists and Dictionaries

  • All about Python Modules and how to install them

  • How to install Python locally

  • How to write your first script in Python

  • To complete your first Python project


Advanced PivotTables

  • How to do a PivotTable (a quick refresher)

  • How to combine data from multiple worksheets for a PivotTable

  • Grouping, ungrouping and dealing with errors

  • How to format a PivotTable, including adjusting styles

  • How to use the Value Field Settings

  • Advanced Sorting and Filtering in PivotTables

  • How to use Slicers, Timelines on multiple tables

  • How to create a Calculated Field

  • All about GETPIVOTDATA

  • How to create a Pivot Chart and add sparklines and slicers

  • How to use 3D Maps from a PivotTable

  • How to update your data in a PivotTable and Pivot Chart

  • All about Conditional Formatting in a PivotTable

  • How to create amazing looking dashboards


Power Pivot, Power Query and DAX

  • How to get started with Power Query

  • How to connect Excel to multiple workbooks

  • How to get data from the web and other sources

  • How to merge and append queries using Power Query

  • How the Power Pivot window works

  • How to set up and manage relationships in a data model

  • How to create a PivotTable to display your data from the Power Pivot data model

  • How to add calculated columns using DAX

  • How to use functions such as CALCULATE, DIVIDE, DATESYTD in DAX

  • All about creating Pivot Charts and PivotTables and using your data model

  • How to use slicers to adjust the data you display


Power BI

  • What 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:

  1. 21+ hours of video tutorials

  2. 200+ individual video lectures

  3. Exercise files to practice what you learned

  4. Certificate of completion


Here’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

See more

FAQ

FAQ area empty

Course curriculum

53 chương 296 bài học 29 giờ 7 phút 7 bài học thử miễn phí
Chương 1 Advanced PivotTables: Introduction
Chương 2 Advanced PivotTable: Importing Data
Chương 3 Advanced PivotTable: Preparing Data for Analysis
Chương 4 Advanced PivotTable: Creating and Manipulating PivotTables
  • Creating and Manipulating a PivotTable 12:27
  • Combining Data from Multiple Worksheets 12:02
  • Grouping and Ungrouping 11:01
  • Report Layouts 10:53
  • Formatting Error Values and Empty Cells 8:26
  • Exercise 03 1:35
  • Section Quiz
Chương 5 Advanced PivotTables: Formatting PivotTables
  • PivotTable Styles 11:39
  • Custom Number Formatting 16:25
  • Exercise 04 1:44
  • Section Quiz
Chương 6 Advanced PivotTables: Value Field Settings
  • Summarizing Values 11:25
  • Show Values As 9:12
  • Exercise 05 1:46
  • Section Quiz
Chương 7 Advanced PivotTables: Sorting and Filtering
  • Advanced Sorting 8:40
  • Advanced Filtering 14:04
  • Exercise 06 2:16
  • Section Quiz
Chương 8 Advanced PivotTables: Interacting with PivotTables
  • Inserting and formatting Slicers 13:51
  • Inserting and formatting Timelines 7:06
  • Connecting Slicers to multiple PivotTables 8:10
  • Using Slicers in Protected Workbooks 5:36
  • Exercise 07 2:20
  • Section Quiz
Chương 9 Advanced PivotTables: Calculations
  • Creating a Calculated Field 8:25
  • Creating a Calculated Item 8:09
  • Solve Order and List Formulas 10:38
  • GETPIVOTDATA 9:25
  • Exercise 08 6:41
  • Section Quiz
Chương 10 Advanced PivotTables: Pivot Charts
  • Creating a Pivot Chart 11:30
  • Formatting a Pivot Chart - Part 1 15:43
  • Formatting a Pivot Chart - Part 2 15:28
  • Creating a Map Chart using Pivot Data 12:01
  • Dynamic Chart Titles 13:50
  • Include a Sparkline with your PivotTable 10:40
  • Exercise 09 7:45
  • Section Quiz
Chương 11 Advanced PivotTables: Conditional Formatting
  • Highlighting Cell Rules 9:32
  • Graphical Conditional Formats 10:06
  • Conditional Formatting and Slicers 6:28
  • Exercise 10 2:26
  • Section Quiz
Chương 12 Advanced PivotTables: Dashboards
  • Creating an Interactive Dashboard - Part 1 23:12
  • Creating an Interactive Dashboard - Part 2 13:55
  • Updating Pivot Charts and PivotTables 4:24
  • Exercise 11 3:26
Chương 13 Course Close
  • Course Close 0:37
Chương 14 Introduction to PowerPivot and PowerQuery
  • Welcome and Overview 3:02
  • WATCH ME: Essential Information for a Successful Training Experience 2:11
  • DOWNLOAD ME: Course Exercise Files
  • DOWNLOAD ME: Course Demo Files
  • What is Power Query? 5:24
  • What is Power Pivot? 3:43
  • Section Quiz
Chương 15 Getting Started with Power Query
  • Exploring the Power Query Editor 6:29
  • Common Power Query Transformations 8:59
  • Editing an Existing Query 9:55
  • Import Multiple Files from a Folder 11:06
  • Connect to Data in Another Excel Workbook 9:37
  • IMPORTANT: Checking the Location of your Query's Source 3:34
  • Get Data From the Web 5:39
  • Practise Exercise 1:05
  • Section Quiz
Chương 16 Useful Power Query Features
  • Unpivoting Columns 5:32
  • Combine Data from Multiple Tables with Merge Queries 6:11
  • Use Merge Queries to Compare Two Tables 5:03
  • Stack Data into One Table with Append Queries 6:02
  • Duplicating and Referencing Queries 9:10
  • Grouping and Aggregating Data 7:53
  • Conditional Columns in Power Query 7:14
  • Practise Exercise 1:14
  • Section Quiz
Chương 17 Creating the Data Model
  • Enable the Power Pivot Add-In 1:54
  • Understanding the Power Pivot Window 4:42
  • Creating Relationships Between Tables 5:03
  • Managing the Relationships of the Model 3:01
  • Creating a PivotTable from the Data Model 4:42
  • Hide Fields from Client Tools 3:19
  • Grouping Queries 4:33
  • Practise Exercise 0:49
  • Section Quiz
Chương 18 Introduction to DAX
  • Why use DAX? 3:14
  • Creating Calculated Columns with DAX 5:09
  • Creating your First DAX Measure 6:17
  • The COUNTROWS Function 5:09
  • SUMX and RELATED Functions 4:45
  • Practise Exercise 0:49
  • Section Quiz
Chương 19 More DAX Measures
  • Create a Date Table in Power Pivot 8:46
  • The CALCULATE Function 3:31
  • The DIVIDE Function 3:09
  • Using the DATESYTD Function 2:20
  • Calculate the Percentage of a Total 6:17
  • Practise Exercise 1:05
  • Section Quiz
Chương 20 Using PivotTables and Slicers
  • Create PivotTables and PivotCharts 11:57
  • Using Slicers with your PivotTables 6:21
  • Create a Top 10 PivotTable 8:25
  • Practise Exercise 0:47
  • Section Quiz
Chương 21 PowerPivot, PowerQuery and Dax: Closing
  • Wrap Up 1:26
Chương 22 Introduction to Power BI
  • Welcome and Overview 2:51
  • WATCH ME: Essential Information for a Successful Training Experience 2:03
  • DOWNLOAD ME: Course Exercise File
  • DOWNLOAD ME: Course Demo Files (Section 2-4)
  • DOWNLOAD ME: Course Demo Files (Section 5-8)
  • Downloadable Course Transcript
  • What is Power BI? 4:22
  • Install Power BI Desktop 2:08
  • A Tour of BI Desktop 6:54
  • Explore Commonly Used Power BI Options 5:23
  • Section Quiz
Chương 23 PowerBI: Getting and Transforming Data
  • Import Files from a Folder into Power BI Desktop 12:29
  • Get Data from Excel and Text Files 7:08
  • Changing Query Course 4:14
  • Reference Queries to Create Additional Lookup Tables 6:36
  • Merge Queries in Power Query 6:57
  • Prevent Queries from Loading into Power BI Desktop 3:05
  • Practise Exercise 0:37
  • Section Quiz
Chương 24 Power BI: Data Modelling
  • Create Relationships Between Tables 6:49
  • Create a Dynamic List of Dates 8:53
  • Create Additional Date Columns for Analysis 9:13
  • Sort the Month and Weekday Names Correctly 2:47
  • Mark the Table as a Date Table 1:03
  • Hide Unnecessary Fields from Report View 4:59
  • Practise Exercise 0:32
  • Section Quiz
Chương 25 Introduction to DAX Measures
  • Calculate the Total Revenue 6:39
  • Count the Total Rows of a Table 1:56
  • Use the CALCULATE Function 5:33
  • Calculate the Total Revenue Last Year 3:38
  • Difference Compared to Last Year 5:23
  • Practise Exercise 0:36
  • Section Quiz
Chương 26 Power BI: Adding Visualizations to your Report
  • Show Summary Information with Cards 6:48
  • Columns Charts to Compare Values 7:33
  • Map Visual to Plot Geographic Data 4:46
  • Slicers to Filter your Reports 8:05
  • KPI Card to Measure Performance Against a Goal 7:16
  • Line Graphs to Visualise Trend 5:59
  • Show Detail with the Matrix 9:45
  • Top N Lists with the Table Visualisation 7:48
  • Practise Exercise 0:37
  • Section Quiz
Chương 27 Power BI: Report Design
  • Adding Text Boxes and Shapes 9:55
  • Use Themes 7:41
  • Conditional Formatting 7:59
  • Practise Exercise 0:34
  • Section Quiz
Chương 28 Power BI: Editing Interactions and Filters
  • Edit the Interactions between your Visualisations 9:45
  • The Filter Pane to Filter at Any Level 7:03
  • Drill Through to More Detail 8:16
  • Practise Exercise 0:35
  • Section Quiz
Chương 29 Power BI Service
  • Publish Your Report to the Power BI Service 6:29
  • Different Ways to Share your Power BI Report 5:25
  • Practise Exercise 0:48
  • Section Quiz
Chương 30 Closing
  • Wrap Up 1:07
Chương 31 Python: The Workplace Tech Divide
  • Which Side of the Divide Are You On? 1:16
  • WATCH ME: Essential Information for a Successful Training Experience 2:11
  • DOWNLOAD ME: Course Exercise Files
  • DOWNLOAD ME: Course Project Files
  • Downloadable Course Transcripts
  • Beginners Are Welcome 1:11
  • Course Overview 1:01
Chương 32 Introduction to Python
  • What is Python? 2:09
  • Python's Comparison to Other Programming Languages 3:59
  • Examples of Python in the Workplace 1:10
  • The Easiest Place to Practice Python 1:37
  • Create an Account Online 1:35
Chương 33 Python: Basic Data Types
  • Python Data Types 1:35
  • Strings 6:26
  • Integers 3:58
  • Floats 2:53
  • Boolean 4:13
  • Data Types Exercise 8:25
  • Section Quiz
Chương 34 Python Built-In Functions
  • What is a Built-In Function? 1:07
  • Where to Look For Built-In Functions 2:12
  • Most Common Built In Functions 4:14
  • Built In Functions Exercise 3:05
  • Section Quiz
Chương 35 Variables and Functions
  • Variables and Functions 4:12
  • Storing Values as Variables 5:02
  • Comparing Variables with Operators 6:26
  • Basic Expressions 5:29
  • Functions 8:45
  • Commenting 2:51
  • Variables and Functions Exercise 6:42
  • Section Quiz
Chương 36 Errors and Debugging
  • What is an Error? 1:39
  • Reading a Stack Trace 5:00
  • Print Function 2:15
  • Try and Except 4:17
  • You Are Not Alone 2:54
  • Errors Exercise 4:29
  • Section Quiz
Chương 37 Python Keywords
  • Python Keywords 2:23
  • Common Keywords 5:27
  • Global 3:38
  • Keywords Exercise 3:06
  • Section Quiz
Chương 38 If-Else Statements
  • Basic Logic 1:45
  • Syntax and Inline Evaluation 4:16
  • Value Evaluation 3:37
  • Complex If-Else Statements 6:44
  • If-Else Exercises 5:33
  • Section Quiz
Chương 39 Storing Complex Data
  • Advanced Data Types 2:03
  • Lists 12:01
  • Dictionaries 8:11
  • Looping: Lists 11:19
  • Looping: Dictionaries 4:28
  • Advanced Data Exercise 5:52
  • Section Quiz
Chương 40 Python Modules
  • Python Modules 5:30
  • Python Built In Modules 5:28
  • Importing Modules 4:34
Chương 41 Installing Python and Modules
  • Python Environments 2:01
  • Mac 4:47
  • Windows 3:00
  • IDEs 6:18
  • Python Idle 12:35
  • Managing files and folders 10:52
  • Executing Scripts 10:29
  • PIP 4:03
Chương 42 Project: Automate Data Updates For a Spreadsheet
  • Project Introduction 2:12
  • Setting Up Project 9:39
  • Reading and Writing to Excel Files 14:36
  • Working with CSV Files 11:41
  • Dynamic File Paths 7:02
  • Transform and Validate Transactions 14:00
  • Transfer and Save Transactions 13:14
  • Clean Up the Code 11:52
  • Hardening the Script 7:34
Chương 43 Course Close
  • What's Next? 0:43
Chương 44 Introduction to Alteryx for Beginners
  • Introduction 4:02
  • WATCH ME: Essential Information for a Successful Training Experience 2:04
  • Course Instructor Files
  • Alteryx Essentials 5:47
  • Data Types 101 4:39
  • Getting Started with the Alteryx Designer 8:55
  • Building a Workflow in Designer 9:26
  • The Favorites Tools 9:55
Chương 45 Alteryx: Data Blend & Prep
  • Data filtering for Beginners 11:23
  • Intro to Alteryx for Excel Users 10:13
  • Alteryx for the SQL Analyst 11:21
  • Introduction to Basic Functions - Part 1 15:41
  • Introduction to Basic Functions - Part 2 9:23
  • Introduction to Basic Functions - Part 3 6:53
Chương 46 Alteryx: Data Parsing
  • Basic Parsing Methods 12:00
  • Basic Parsing Methods w/ Dynamic Renaming 8:28
  • Basic Vlookup and Append 10:56
  • Working on Multiple Fields in Alteryx 10:18
  • Build an Alteryx Workflow 10:57
  • Basic Tips and Tricks 10:00
Chương 47 Reporting
  • Alteryx Best Practices - Visualizing Data 9:21
  • Alteryx Best Practices - Visualizing Data with Texts and Charts 12:03
  • Alteryx Best Practices - Layouts and Rendering 8:30
Chương 48 Alteryx: Analytic Apps
  • Introduction to Analytic Apps 9:50
  • Introduction to Macros 8:35
Chương 49 Alteryx: Spatial Data Analysis
  • Intro to Data Analysis - Working Spatial Data 11:00
  • Intro to Data Analysis - Measuring 13:53
  • Intro to Data Analysis - Spatial Objects 10:34
Chương 50 Alteryx: Analytics
  • Introduction to Analytics 12:45
  • Introduction to K-Centroid Clustering 11:28
  • Introduction to K-Nearest Neighbor 9:36
  • Introduction to Market Basket Analysis 11:00
  • Introduction to Logistic Regression Analysis 9:36
  • Introduction to Linear Regression Analysis 8:30
  • Introduction to Tree-based Models Part 1 12:00
  • Introduction to Tree-based Models Part 2 10:31
Chương 51 Course Close
  • Course Close 0:22
  • Course Quiz
Chương 52 BONUSES
  • Ten Excel Tips and Tricks - Part 1 43:23
  • Ten Excel Tips and Tricks - Part 2 40:36
  • Ten Excel Tips and Tricks - Part 3 37:09
Chương 53 Additional Training
  • READ ME: This Additional Training Is Optional

Requirment

  • 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

Outcomes

  • 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

Instructor

...
Simon Sez IT

0.0

  • ... 0 Student
  • ... 2 Courses
  • ... 0 review

View Details

Reviews

Rate this course :

Remove all