Data Analysts Toolbox: Excel, Python, Power BI

Data Analysts Toolbox: Excel, Python, Power BI

by Simon Sez IT
Released December 2020
Publisher(s): Packt Publishing
ISBN: 9781801075329

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to live online training experiences, plus books, videos, and digital content from O’Reilly and nearly 200 trusted publishing partners.

Start your free trial

Video description

Discover how to use Excel, Python, and Power BI, to perform complex data analysis

About This Video

  • Become confident in cleaning, sorting, and linking data from various sources
  • Learn how to write flawless Python scripts for updating the data in a spreadsheet
  • Get ready to create amazing visuals, such as clustered column charts, maps, and trend graphs

In Detail

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.

Who this book is for

This course is aimed at intermediate Excel users who want to learn Python, Power BI, Power Pivot, and advanced pivot tables to analyze and visualize data. Working knowledge of Excel is required to get started with this course.

Publisher resources

Download Example Code

Table of contents

  1. Chapter 1 : Advanced Pivot Tables: Introduction
    1. Introduction to Advanced Pivot Tables
    2. Pivot Tables Recap
  2. Chapter 2 : Advanced Pivot Tables: Importing Data
    1. Importing Data from a Text File
    2. Importing Data from Access
    3. Exercise
  3. Chapter 3 : Advanced Pivot Tables: Preparing Data for Analysis
    1. Cleaning Data
    2. Tabular Data
    3. Exercise
  4. Chapter 4 : Advanced Pivot Tables: Creating and Manipulating Pivot Tables
    1. Creating and Manipulating a Pivot Table
    2. Combining Data from Multiple Worksheets
    3. Grouping and Ungrouping
    4. Report Layouts
    5. Formatting the Error Values and Empty Cells
    6. Exercise
  5. Chapter 5 : Advanced Pivot Tables: Formatting a Pivot Table
    1. Pivot Table Styles
    2. Custom Number Formatting
    3. Exercise
  6. Chapter 6 : Advanced Pivot Tables: Value Field Settings
    1. Summarizing Values
    2. Show Values As
    3. Exercise
  7. Chapter 7 : Advanced Pivot Tables: Sorting and Filtering
    1. Advanced Sorting
    2. Advanced Filtering
    3. Exercise
  8. Chapter 8 : Advanced Pivot Tables: Interacting with a Pivot Table
    1. Inserting and Formatting Slicers
    2. Inserting and Formatting Timelines
    3. Connecting Slicers to Multiple Pivot Tables
    4. Using Slicers in a Protected Workbook
    5. Exercise
  9. Chapter 9 : Advanced Pivot Tables: Calculations
    1. Creating a Calculated Field
    2. Creating a Calculated Item
    3. Solve Order and List Formulas
    4. GETPIVOTDATA
    5. Exercise
  10. Chapter 10 : Advanced Pivot Tables: Pivot Charts
    1. Creating a Pivot Chart
    2. Formatting a Pivot Chart - Part 1
    3. Formatting a Pivot Chart - Part 2
    4. Creating a Map Chart Using Pivot Data
    5. Creating a Dynamic Chart Title
    6. Include a Sparkline with a Pivot Table
    7. Exercise
  11. Chapter 11 : Advanced Pivot Tables: Conditional Formatting
    1. Highlighting Cell Rules
    2. Graphical Conditional Formats
    3. Conditional Formatting and Slicers
    4. Exercise
  12. Chapter 12 : Advanced Pivot Tables: Dashboards
    1. Creating an Interactive Dashboard - Part 1
    2. Creating an Interactive Dashboard - Part 2
    3. Updating Pivot Charts and PivotTables
    4. Exercise
  13. Chapter 13 : Advanced Pivot Tables: Summary
    1. Summary
  14. Chapter 14 : Introduction to Power Pivot and Power Query
    1. Welcome and Overview
    2. What is Power Query?
    3. What is Power Pivot?
  15. Chapter 15 : Getting Started with Power Query
    1. Exploring the Power Query Editor
    2. Common Power Query Transformations
    3. Editing an Existing Query
    4. Importing Multiple Files from a Folder
    5. Connecting to Data in another Excel Workbook
    6. Important: Checking the Location of Your Query's Source
    7. Retrieving Data from the Web
    8. Practice Exercise
  16. Chapter 16 : Useful Power Query Features
    1. Unpivoting Columns
    2. Combining Data from Multiple Tables with Merge Queries
    3. Using Merge Queries to Compare Two Tables
    4. Stacking Data into One Table with Append Queries
    5. Duplicating and Referencing Queries
    6. Grouping and Aggregating Data
    7. Adding Conditional Columns in Power Query
    8. Practice Exercise
  17. Chapter 17 : Creating a Data Model
    1. Enabling the Power Pivot Add-in
    2. Understanding the Power Pivot Window
    3. Creating Relationships between Tables
    4. Managing Relationships of the Model
    5. Creating a Pivot Table from the Data Model
    6. Hiding Fields from the Client Tools
    7. Grouping Queries
    8. Practice Exercise
  18. Chapter 18 : Introduction to Data Analysis Expressions (DAX)
    1. Why use Data Analysis Expressions (DAX)?
    2. Creating Calculated Columns with Data Analysis Expressions (DAX)
    3. Creating the First Data Analysis Expressions (DAX) Measure
    4. Using the COUNTROWS Function
    5. Using the SUMX and RELATED Functions
    6. Practice Exercise
  19. Chapter 19 : More Data Analysis Expressions (DAX) Measures
    1. Creating a Date Table in Power Pivot
    2. Using the CALCULATE Function
    3. Using the DIVIDE Function
    4. Using the DATESYTD Function
    5. Calculating the Percentage of a Total
    6. Practice Exercise
  20. Chapter 20 : Using Pivot Tables and Slicers
    1. Creating Pivot Tables and Pivot Charts
    2. Using Slicers with Pivot Tables
    3. Creating a Top 10 Pivot Table
    4. Practice Exercise
  21. Chapter 21 : Power Pivot, Power Query, and Data Analysis Expressions (DAX): Summary
    1. Power Pivot, Power Query, and Data Analysis Expressions (DAX): Summary
  22. Chapter 22 : Introduction to Power BI
    1. Welcome and Overview
    2. What is Power BI?
    3. Installing Power BI Desktop
    4. Tour of Power BI Desktop
    5. Exploring the Commonly Used Power BI Options
  23. Chapter 23 : Power BI: Getting and Transforming Data
    1. Importing Files from a Folder into Power BI Desktop
    2. Getting Data from Excel and Text Files
    3. Referencing Queries to Create Additional Lookup Tables
    4. Merging Queries in Power Query
    5. Preventing Queries from Loading into Power BI Desktop
    6. Practice Exercise
  24. Chapter 24 : Power BI: Data Modelling
    1. Creating a Relationship between Tables
    2. Creating a Dynamic List of Dates
    3. Creating Additional Date Columns for Analysis
    4. Sorting the Month and Weekday Names Correctly
    5. Marking the Table as a Date Table
    6. Hiding Unnecessary Fields from the Report View
    7. Practice Exercise
  25. Chapter 25 : Introduction to Data Analysis Expressions (DAX) Measures
    1. Calculating Total Revenue
    2. Counting the Total Rows of a Table
    3. Using the CALCULATE DAX Function
    4. Calculating the Total Revenue for Last Year
    5. Difference Compared to Last Year
    6. Practice Exercise
  26. Chapter 26 : Power BI: Adding Visualizations to Your Report
    1. Showing Summary Information with Cards
    2. Comparing Values with Columns Charts
    3. Mapping Visual to Plot Geographic Data
    4. Filtering Reports with Slicers
    5. Key Performance Indicator (KPI) Card to Measure Performance against a Goal
    6. Line Graphs to Visualize a Trend
    7. Showing Details with the Matrix
    8. Top N Lists with Table Visualization
    9. Practice Exercise
  27. Chapter 27 : Power BI: Report Design
    1. Adding Text Boxes and Shapes
    2. Using Themes
    3. Conditional Formatting
    4. Practice Exercise
  28. Chapter 28 : Power BI: Editing Interactions and Filters
    1. Editing Interactions between Visualizations
    2. Filter Pane to Filter at any Level
    3. Drilling through to More Detail
    4. Practice Exercise
  29. Chapter 29 : Power BI Service
    1. Publishing a Report to the Power BI Service
    2. Different Ways to Share a Power BI Report
    3. Practice Exercise
  30. Chapter 30 : Power BI: Summary
    1. Summary
  31. Chapter 31 : Python: The Workplace Tech Divide
    1. Which Side of the Divide are You on?
    2. Beginners Are Welcome
    3. Course Overview
  32. Chapter 32 : Introduction to Python
    1. What is Python?
    2. Python's Comparison to Other Programming Languages
    3. Examples of Python in the Workplace
    4. The Easiest Place to Practice Python
    5. Creating an Account Online
  33. Chapter 33 : Basic Data Types
    1. Python Data Types
    2. Strings
    3. Integers
    4. Floats
    5. Boolean
    6. Data Types Exercise
  34. Chapter 34 : Python Built-in Functions
    1. What are Built-in Functions?
    2. Where to Look for the Built-in Functions?
    3. Most Common Built-in Functions
    4. Built-in Functions Exercise
  35. Chapter 35 : Variables and Functions
    1. Variables and Functions
    2. Storing Values as Variables
    3. Comparing Variables with Operators
    4. Basic Expressions
    5. Functions
    6. Commenting
    7. Variables and Functions Exercise
  36. Chapter 36 : Errors and Debugging
    1. What is an Error?
    2. Reading a Stack Trace
    3. Print Function
    4. Try and Except
    5. You Are Not Alone
    6. Errors Exercise
  37. Chapter 37 : Python Keywords
    1. Python Keywords
    2. Common Keywords
    3. Global Keywords
    4. Keywords Exercise
  38. Chapter 38 : If-Else Statements
    1. Basic Logic
    2. Syntax and Inline Evaluation
    3. Value Evaluation
    4. Complex If-else Statements
    5. If-else Exercises
  39. Chapter 39 : Storing Complex Data
    1. Advanced Data Types
    2. Lists
    3. Dictionaries
    4. Looping: Lists
    5. Looping: Dictionaries
    6. Advanced Data Exercise
  40. Chapter 40 : Python Modules
    1. Python Modules
    2. Python Built-in Modules
    3. Importing Modules
  41. Chapter 41 : Installing Python and Modules
    1. Python Environments
    2. Installing Python on Mac
    3. Installing Python on Windows
    4. Installing Python on Integrated Development Environments (IDEs)
    5. Installing Python on Integrated Development and Learning Environment (IDLE)
    6. Managing Files and Folders
    7. Executing Scripts
    8. Pip
  42. Chapter 42 : Project: Automating Data Updates in a Spreadsheet
    1. Project Introduction
    2. Setting up the Project
    3. Reading and Writing to Excel Files
    4. Working with Comma-Separated Values (CSV) Files
    5. Dynamic File Paths
    6. Transforming and Validating Transactions
    7. Transferring and Saving Transactions
    8. Cleaning up the Code
    9. Hardening the Script
  43. Chapter 43 : Summary
    1. What's Next?

Product information

  • Title: Data Analysts Toolbox: Excel, Python, Power BI
  • Author(s): Simon Sez IT
  • Release date: December 2020
  • Publisher(s): Packt Publishing
  • ISBN: 9781801075329