Excel Dashboards : Integrating Power Query, Power Pivot, and Pivot Tables

Course Modules

POWER QUERY

Lesson 1 : Getting data from multiple of source

• Import data from Excel
• Import data from Text or CSV Files
• Import data from Folder

Lesson 2 : Keep the Right Data

• Identify Column Header via Promote and Demote
• Changing Data Type
• Choose Columns
• Remove Blank Rows
• Removing Duplicate Records
• Remove Top/Bottom Rows
• Moving, Renaming Columns
• Reorganize data with Sorting
• Get relevant data with Filter

Lesson 3 : Combine Table

• Joining multiple tables into master table via Append Queries
• Merging Queries on Single or Multiple Matching Columns
• Returning Referenced Details on Those Matched
• Returning Results on Those Not Matched

Lesson 4 : Getting Results

• Close and Load data into Excel Table from Power Query
• Refresh query to get latest data from source

Lesson 5 : Data Reshaping

• Replace Value
• Unpivoting Columns
• Splitting Column into Multiple Columns
• Merging Columns
• Fill empty cells with data above and below

Lesson 6: Output Different Result

• Manage Text Column
  • Converting Text to Uppercase, Lowercase
  • Clean up non-printable characters with TRIM and CLEAN
• Manage Date Column
  • Calculate Age
  • Extract Year, Quarter, Month & Day

Lesson 7 :  Managing Additional Column

• Create Custom Columns
• Create Conditional Column
• Column from Examples

Lesson 8 :  Managing Data Source

• Data Source Settings
• Change Source Location

POWER PIVOT

Lesson 1 : Starting with PowerPivot

• Add-ins Power Pivot from Options
• Launch the PowerPivot window
• Changing between Data & Diagram view
• Load from Power Query

Lesson 2 : Manage Data, Relationships & Hierarchy

• Set Data Type and Format
• Understanding Types of Relationships
• Create a Relationship Between Tables
• View and Edit Relationships
• Delete Relationships
• Hierarchy
  • Create & rename Hierarchy
  • Adding Column to Hierarchy
  • Remove Column from Hierarch

Lesson 3 : Calculated Column & Measures

• Building Calculated Columns within a Table
• Building calculate Column across multiple table
• Creating a New Measure
• Formatting a Measure
• Modify and Delete Measures

Lesson 4 : Date Table

• Create a Date Table
• Mark as Date Table

PIVOT TABLE & CHARTS

Lesson 1 : Create & Quick Formatting Pivot Table

• Create Pivot Table from Data Model
• Configure Report Layout
• Setting Pivot Table Style
• Turn On summary with Subtotal and Grand total
• Navigating report using Expanding and Collapsing

Lesson 2 : Extracting Meaningful Data

• Rearranging Top and Bottom value
• Enabling Multiple Filer
• Label Filter
• Value Filter

Lesson 3 : Analyzing Pivot Table

• Changing Summary Value with different formula
• Measuring Value as Percentage
• Preparing Accumulated Value
• Comparing values against previous and next category
• Adding calculation for multiple value fields

Lesson 4 : Getting Latest Data into Pivot Table Report

• Refreshing Pivot Table for additional data

Lesson 5 : Sporting trends with Conditional Formatting

• Highlighting the Top 10 analysis
• Spotting larger or smaller numbers in a range
• Applying indicator icon to visualize range of value

Lesson 6 : Showcase Meaningful data as Pivot Chart

• Getting the right chart to visualize the data
• Customizing Pivot Chart Layouts and style
• Joining two type of chart to represent 2 corresponding value

Lesson 7 : Mini charts for series of data with Sparkline

• Show trends in a series of values with Sparkline – Line
• Highlight maximum or minimum values with Sparkline – Column
• Quick formatting multiple sparkline with ONE click

Lesson 8 : Sketch Dashboard Layout

• Compiling all visual representation into one page
• Create best layout by position all items
• Standardize the Formatting of the page
• Linking important value from pivot table into Dashboard

Lesson 9 : Interactive Selection with Slicer and Timeline

• Dynamic filter to update entire dashboard with slicer
• Focusing on a selected time period with Timeline

Lesson 10 : Exporting Excel Dashboard into PowerPoint

• Linking Excel Dashboard in Power Point
• Setting interaction with OLE Action Verb
• Setting Up Presenter View
• Presenting Dashboard