Building Bi With Pivot Tables

  1. Home
  2. Pivot-tables
  3. Building Bi With Pivot Tables

Tags: Pivot Tables

Sourcing data, Cleansing it, and Analyzing it through Excel pivot tables and dashboards. Master reporting & data viz

Last updated 2022-01-10 | 4.7

- Build Dashboards and visualizations with PivotTables
- Build reports and quickly summarize 10s to 1
- 000s of rows of data quickly
- Source and clean up source data for reporting

What you'll learn

Build Dashboards and visualizations with PivotTables
Build reports and quickly summarize 10s to 1
000s of rows of data quickly
Source and clean up source data for reporting
Present the data in a way that is easy to use for analysis by presenting data in both tabular and visually
Analyze the data and drill in and out of data
Source data
cleanse it
and analyze it through pivot tables and dashboards

* Requirements

* Before starting this course
* you should be comfortable working in Excel. Knowledge of a variety of formula can be helpful
* although is by no means necessary. While those comfortable with Pivot Tables are likely to pick up new tricks to make their lives easier
* the course is designed to take someone with absolutely no Pivot Table knowledge and teach them how to collect
* clean and set up the data
* present it in Pivot Tables and build dashboards using Pivot Charts.

Description

Building Business Intelligence with Pivot Tables is an online video course that is perfect for anyone looking to build reports and quickly summarize 10s to 1,000s of rows of data quickly. This course will provide the analyst with the best ways to source and clean up source data for reporting. As the data is cleansed, the course will show you how to present the data in a way that is easy to use for analysis by presenting data in both tabular and visually. The course will further explore the best ways to analyze the data and drill in and out of data By the end of this course, you will feel confident sourcing data, cleansing it, and analyzing it through pivot tables and dashboards.

This is a course aimed squarely at the beginner/intermediate Excel users looking to take their skills to the next level. Before starting this course, you should be comfortable working in Excel. Knowledge of a variety of formula can be helpful, although is by no means necessary. While those comfortable with Pivot Tables are likely to pick up new tricks to make their lives easier, the course is designed to take someone with absolutely no Pivot Table knowledge and teach them how to collect, clean and set up the data, present it in Pivot Tables and build dashboards using Pivot Charts. 

The instructor, Ken Puls, is an Excel MVP, blogger, conference speaker, and co-author of "M is for Data Monkey" - a guide to the M language in Excel Power Query.

Who this course is for:

  • This is a course aimed squarely at the intermediate-to-advanced Excel user looking to take their skills to the next level.

Course content

8 sections • 87 lectures

Intro - The value of Pivot Tables 5 lectures

Business Intelligence Lifecycle Preview 06:56

Why You Care About PivotTables Preview 07:02

Creating Your First Pivot Table Preview 03:05

DEMO: Creating Your First PivotTable Preview 10:34

Module 1 Quiz

Pivot Compliant Data Sets 11 lectures

Required Data Layout Preview 15:18

Connecting To Your Source Data Preview 07:00

Issues Using Excel Ranges Preview 03:56

Using Excel Tables Preview 02:34

Building PivotTables Against Excel Tables Preview 08:16

Why Excel Tables Are Better Than Ranges Preview 01:22

Using Data Direct From Databases Preview 04:27

Sourcing Data Direct from Databases to PivotTables Preview 02:21

Sourcing Data Direct from Databases to a Table Preview 03:03

Sourcing Data Direct from the Database to the Data Model Preview 06:07

Module 2 Quiz

Controlling PivotTable Look & Feel 11 lectures

Value Field Formats Preview 05:29

Modifying Column Names and Number Formats Preview 05:50

Report Layouts Preview 06:05

Changing PivotTable Layouts Preview 07:21

Grouping Data Preview 04:56

Grouping Data in PivotTables Preview 11:17

PivotTables Styles Preview 02:20

Creating Custom PivotTables Styles Preview 04:53

Sorting PivotTables Preview 01:45

Sorting Methods in PivotTables Preview 06:01

Module 3 Quiz

Creating Compliant Data Sets 19 lectures

Using Power Query to Get And Transform Your Data Preview 13:52

Cleaning up a Text File with Power Query Preview 17:11

Appending Data Tables Preview 02:26

Simple Append Operations Preview 06:48

Consolidating flat files from folder Preview 04:34

Consolidating a Folder of CSV Files Preview 16:33

Consolidating Excel Data Preview 01:09

Consolidating External Excel Data Files Preview 06:05

Get Data From Active Workbook Preview 01:25

Consolidating Data From Within the Active Work File Preview 04:15

UnPivoting Data Preview 02:02

Unpivoting simple data sets Preview 06:07

Flattening Data Sets Preview 02:38

Flattening Tables with VLOOKUP Preview 03:09

6 Ways To Join - Foundation Preview 01:37

Flattening Tables with Power Query - The basics Preview 04:26

Join Types Preview 05:22

6 Ways To Join With Power Query Preview 06:00

Module 4 Quiz

Changing Aggregations 15 lectures

Sum, Count & Others Preview 06:28

Introduction To Changing Aggregations Preview 03:10

Show Values As Preview 01:38

Running Totals Preview 05:05

Difference & % Difference From Preview 00:55

Difference from X & % Difference from X Preview 05:36

Ranking Items Preview 00:37

Ranking Items in PivotTables Preview 04:10

Showing the Top/Bottom X Records Preview 01:20

Displaying Top/Bottom X Preview 05:16

Calculated Fields Preview 03:17

Working With Calculated Fields Preview 07:43

Calculated Items Preview 02:12

Working With Calculated Items Preview 02:51

Module 5 Quiz

Slicing and Filtering 11 lectures

Filtering with Classic Controls Preview 03:01

Working With Pivot Table Filter FIelds Preview 03:58

Filtering With Slicers Preview 03:09

Working With Slicers Preview 11:24

Filtering With Timelines Preview 01:16

Working With Timelines Preview 07:18

The "Show Details" Feature Preview 02:13

The "Show Details" Feature - DEMO Preview 04:03

Keep Your Pivots Looking Nice Preview 02:14

Toggling Pivot Table Options Preview 04:48

Module 6 Quiz

Building Pivot Based Dashboards 9 lectures

Conditional Formatting On Pivots Preview 01:51

Conditional Formatting - DEMO Preview 08:17

Keeping Pivots In Sync Preview 03:30

Linking One Slicer to Multiple Pivots Preview 04:46

Tricks For Extracting Key Info Preview 03:15

Extracting Data From Pivots And Slicers Preview 13:48

Working With Pivot Charts Preview 03:04

Building Pivot Charts Preview 15:43

Module 7 Quiz

Data Refresh and Security 6 lectures

Driving Pivot Table Refresh Preview 04:32

Refresh Options for External Data Sets Preview 02:37

Refresh Options for Local Data Sets Preview 07:04

How Secure Is Your Data? Preview 03:52

DEMO: Exposing Data Preview 04:30

Module 8 Quiz