Instructor for this course

Learn how to explore and analyze your data by creating an automated business dashboard in Excel using pivot tables, Power Query, and a little Visual Basic. Using these lessons, you can create a dynamic, automated template to generate reports and gain insights from your data. Along the way you'll learn how to import and transform external data using using Power Query, design dashboards using Pivot Tables, and add macros and buttons to make the template interactive.

We're going to leverage new functionality in Excel in order to automate data extraction and data cleansing and thus concentrate on turning our data into insights and insights into actions. Let's get started.

Learning Objectives

  • Identify data that is suitable for data analysis and recognize how to transform them as necessary.
  • Discover Excel’s business intelligence capabilities using Power Query, Pivot tables and Pivot Charts
  • Explore Excel’s capabilities to automate data cleansing and transformation using Power Query
  • Explore basic programming concepts using VBA (Visual Basic for Applications) and Power Query
Last updated/reviewed: May 26, 2022

9 Reviews (18 ratings)Reviews

Anonymous Author
I liked that it went through a lot of the functions of Power query and a step by step process of how everything comes together. I would have liked more time spent on how to go through the process for using power query, rather than the creation of the dashboard. I will use this as a baseline on what is possible to manipulate in power query, but I will most likely need to lookup more training to implement for my own data.
Anonymous Author
Too much copy and pasting in this module. Power query seems like too advanced of a tool to use without coding background. I feel like a general overview of what happened would be good as well on top of discussion on when and why you would want to use power query over just sticking with excel. Most of what was done seems like it could have been done in strictly excel.
Anonymous Author
This was not a good course. It was basically a guy narrating while he creates power queries and dashboards without really explaining or teaching along the way.
Anonymous Author
Good overall intro to power query. But the VBA portion was just copying existing code. OK for this level of course since focused on power query.
Member's Profile
Good information on a new tool for me. Generally use Access to link tables, gives another tool that is more helpful for end users.
Anonymous Author
This class was pretty challenging. I would be interested in a course with more focus on creating variations of dashboards.
Member's Profile
There are too many unexplained VBA codes in use, I feel woefully unprepared to build dashboards.
Anonymous Author
While a bit dry the course effectively covered the stated learning objectives.
Member's Profile
Course content was good and the materials provided were helpful.


Course Complexity: Advanced
  • You should know how to use Excel and are familiar with basic formulas and how to build them. 
  • You’ve created basic charts using the standard ones available in Excel, and you’re familiar with Pivot Tables and Pivot Charts but not an expert.
  • Familiarity with Excel add-ins and how to activate them.
  • Familiarity with Excel VBA is ideal but is not a must. 
  • Excel 2010 or later is needed to create Power Query commands and edit them.

Education Provider Information

Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Syllabus
  4:01Introduction to Excel Power Query: Create Automated Business Dashboard
  4:06Getting Started
  4:56Planning our Dashboard
  16:17Preparing and Cleansing Our Data
  5:36Creating our Dashboard
  12:30Designing our Dashboard
  14:31Future - Proofing our Dashboard
  5:54Finishing our Dashboard
  1:08:29Excel Power Query: Create Automated Business Dashboard
  PDFSlides: Excel Power Query: Create Automated Business Dashboard
  PDFExcel Power Query: Create Automated Business Dashboard Glossary/Index
  ZIPExercise Files: Excel Power Query