Data Analysts' Toolbox - Excel, Python, Power BI, Alteryx, Qlik Sense, R, Tableau

Data Analysts' Toolbox - Excel, Python, Power BI, Alteryx, Qlik Sense, R, Tableau

by Simon Sez IT
Released June 2022
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 books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Video description

Being able to understand, harness, and use data is no longer a skill reserved for a handful of well-paid data analysts. It is becoming an essential part of many roles. This 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 to grips with Power Pivot, Power Query, and Data Analysis Expressions (DAX) and discover how to use Power BI to create striking data visualization.

You will then learn the Python programming concepts that will help you write error-free Python scripts for automatically updating data in a spreadsheet.

Next, you will get introduced to Alteryx, Qlik Sense, R programming, and finally, learn how to create stunning visualizations in Tableau Desktop.

By the end of this course, you will be able to confidently analyze and visualize huge sets of data using Excel, Python, Power BI, Alteryx, Qlik Sense, R, and Tableau.

What You Will Learn

  • Discover how to use 3D maps in a pivot table
  • Create pivot charts and pivot tables using your data model
  • Create stunning and interactive dashboards with Power BI
  • Learn to master Qlik Sense
  • Learn R programming using R and R Studio
  • Learn to create stunning visualizations in Tableau Desktop

    • Audience

    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. This course is also designed to get introduced to Alteryx, Qlik Sense, R programming, and Tableau Desktop. Working knowledge of Excel and understanding of data analytics are required to get started with this course.

    About The Author

    Simon Sez IT: Simon Sez IT has offered technical courses since 2008 for individuals, small businesses, and Fortune 500 companies with thousands of employees who can benefit from the easy-to-learn and hands-on software training. It offers over 8000 video tutorials on a range of software programs. Simon Sez IT ensures stress-free eLearning and enhanced employee productivity—whether you implement new software or a technological upgrade in your work environment. With over 600,000 students from 180 countries, Simon Sez IT is the preferred online learning choice for individuals and businesses worldwide.

    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. 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?
    44. Chapter 44 : Introduction to Alteryx for Beginners
      1. Introduction
      2. Alteryx Essentials
      3. Data Types 101
      4. Getting Started with the Alteryx Designer
      5. Building a Workflow in Designer
      6. The Favorites Tools
      7. Data Filtering for Beginners
      8. Introduction to Alteryx for Excel Users
      9. Alteryx for the SQL Analyst
      10. Introduction to Basic Functions - Part 1
      11. Introduction to Basic Functions - Part 2
      12. Introduction to Basic Functions - Part 3
      13. Basic Parsing Methods
      14. Basic Parsing Methods w/ Dynamic Renaming
      15. Basic VLOOKUP and Append
      16. Working on Multiple Fields in Alteryx
      17. Build an Alteryx Workflow
      18. Basic Tips and Tricks
      19. Alteryx Best Practices - Visualizing Data
      20. Alteryx Best Practices - Visualizing Data with Texts and Charts
      21. Alteryx Best Practices - Layouts and Rendering
      22. Introduction to Analytic Apps
      23. Introduction to Macros
      24. Intro to Data Analysis - Working Spatial Data
      25. Introduction to Data Analysis - Measuring
      26. Introduction to Data Analysis - Spatial Objects
      27. Introduction to Analytics
      28. Introduction to K-Centroid Clustering
      29. Introduction to K-Nearest Neighbor
      30. Introduction to Market Basket Analysis
      31. Introduction to Logistic Regression Analysis
      32. Introduction to Linear Regression Analysis
      33. Introduction to Tree-Based Models Part 1
      34. Introduction to Tree-Based Models Part 2
      35. Summary
    45. Chapter 45 : Getting Started in Qlik Sense - Beginner to Master
      1. Introduction to the Course
      2. What Is Qlik Sense?
      3. Qlik Sense Versus QlikView
      4. Different Versions of Qlik Sense
      5. Exploring the Qlik Sense Interface
      6. Loading Data Part 1
      7. Loading Data Part 2
      8. Creating and Loading Apps
      9. Adding Your First Chart
      10. Editing Your Visualization Part 1
      11. Editing Your Visualization Part 2
      12. Publishing and Sharing Apps
      13. Exercise 01: Loading Data
      14. Data Assets in Visualization Part 1
      15. Data Assets in Visualization Part 2
      16. Types of Charts and Graphs Part 1
      17. Types of Charts and Graphs Part 2
      18. Types of Charts and Graphs Part 3
      19. Types of Charts and Graphs Part 4
      20. Types of Charts and Graphs Part 5
      21. Generating Insights and Analysis
      22. Exercise 02: Build a Chart
      23. Exercise 03: Types of Charts and Graphs
      24. Aggregations
      25. Functions and Expressions Part 1
      26. Functions and Expressions Part 2
      27. Date and Time Formatting
      28. Conditional Functions
      29. Data Load Script
      30. Join, Keep, and Concatenate Prefixes
      31. Set Analysis
      32. Exercise 04: Numeric and String Functions
      33. Exercise 05: Inner Join
      34. Geo Analytics in Qlik Sense
      35. Qlik Sense Course Conclusion
    46. Chapter 46 : R Programming for Beginners: Includes R Mini-Project!
      1. Welcome!
      2. Course Overview
      3. Why R?
      4. R for Data Science
      5. Preparing Workspace
      6. Guide to RStudio
      7. Exercise 1 - Introduction to R
      8. Operations-and-Variables
      9. Data Types in R
      10. Coding Style
      11. Comments
      12. Exercise 2 - Basics of R programming
      13. Vector Creation
      14. Selecting Components from a Vector
      15. Labeling Vector Elements
      16. Calculations with Vectors
      17. Base R Functions to Use with Vectors
      18. Comparing Two Vectors
      19. Modifying Vector Components
      20. Exercise 3 - Vectors
      21. Matrix Introduction and Creation
      22. Matrix Metrics and Naming
      23. Selecting Elements
      24. Matrix Arithmetic
      25. Matrices Operations
      26. Matrix Modification
      27. Exercise 4 - Matrices
      28. Array Introduction and Creation
      29. Array Similarities to Matrices
      30. Other Array Operations
      31. Exercise 5 - Arrays
      32. List Introduction and Creation
      33. List Naming
      34. Selecting List Elements
      35. List Manipulation
      36. List Operations
      37. Exercise 6 - Lists
      38. Factor Introduction and Creation
      39. Setting Factor Levels
      40. Ordering Factors
      41. Converting Factors
      42. Other Considerations
      43. Exercise 7 - Factors
      44. Loop Introduction and Creation
      45. If-Else Statements
      46. For Loops
      47. While Loops
      48. Repeat Loops
      49. Loop Comparison
      50. Exercise 8 - Loops
      51. Function Introduction and Creation
      52. Function Arguments
      53. Nested Functions
      54. Global Versus Local Variables
      55. Exercise 9 - Function
      56. Dataframe Introduction and Creation
      57. Tidyverse
      58. Tibbles
      59. Tidy Data
      60. Dplyr and Data Transformation
      61. Summarizing Dataframes
      62. Exercise 10 - Dataframe
      63. Introduction to Mini-Project
      64. Importing Data
      65. Comprehending the Dataset
      66. Tidying Data
      67. Grouping Time Series Analysis Data
      68. Data Visualization
      69. Statistical Analysis
      70. Exercise 11 Mini-Project
      71. Great Job and Farewell!
    47. Chapter 47 : Tableau for Beginners - Getting Started in Tableau
      1. Course Introduction
      2. Tableau Introduction
      3. Tableau Product Suite Introduction
      4. Business Intelligence Introduction
      5. Exploring Tableau
      6. Tableau Data Concepts
      7. Connecting to Data Sources
      8. Data Sources in Tableau
      9. Tableau Workspace
      10. Creating a New View
      11. Using Multiple Data Sources
      12. Exercise 1
      13. Selecting a Chart Type Part 1
      14. Selecting a Chart Type Part 2
      15. Building a View Part 1
      16. Building a View Part 2
      17. Designing Callout Numbers and Tables
      18. Histograms and Whisker Plots
      19. Scatter Plot and Correlation Matrix
      20. Spatial Charts
      21. Creating a Dashboard
      22. Presenting a Story
      23. Publishing and Sharing a Workbook
      24. Exercise 2
      25. Exercise 3
      26. Using Expressions in Tableau
      27. Numeric Expressions and Automatic Calculations
      28. String Expressions
      29. Conditional Expressions
      30. Analytical Functions
      31. Exercise 4
      32. Exercise 5
      33. Course Conclusion

    Product information

    • Title: Data Analysts' Toolbox - Excel, Python, Power BI, Alteryx, Qlik Sense, R, Tableau
    • Author(s): Simon Sez IT
    • Release date: June 2022
    • Publisher(s): Packt Publishing
    • ISBN: 9781801075329