Instructor for this course
more

The course is presented in four parts.

First, brief introduction to Power Query and discuss reasons it has become very popular in Finance and Accounting:

  • Consolidate various types of data source files.
  • Instant consolidation allows accountants to consolidate files in seconds.
  • Facilitate self-service reporting by using Macro and Timeline.
  • No more need to request for ad-hoc reports from report builders!

Second part demonstrates 7 intermediate formulas in data transformation with Power Query:

  • 5 Text Formulas.
  • Date Formula.
  • Conditional Formula.

Third, we accelerate our learning with 6 advanced functions that dramatically increase our productivity. They relate to:

  • How to consolidate files from same sources / file types.
  • How to consolidate files from different sources / file types.
  • How to fill gaps in rows.
  • How to SUM without using Pivot Table.
  • How to filter rows using a changing value, and
  • How to quickly change folder.

Last, the course culminates through introducing the concept of “Instant Consolidation” and “Self-service Reporting”, and teaches the expert level of automating the whole data refresh cycle through controlled period selection from a Timeline!

Learning Objectives

  • Explore to deploy a fully instant consolidation of data from different data sources.
  • Identify to build a fully automated self-service reporting model from scratch.
  • Recognize 7 essential formulas in Power Query.
  • Explore 6 powerful functions in Power Query.
  • Discover to enhance your financial reports with a Timeline that synchronizes your report title and statements.
Last updated/reviewed: May 14, 2022

5 Reviews (35 ratings)Reviews

5
Anonymous Author
Really excited to get to use power query going forward. The course is clear and builds from the previous lesson. Shows the steps to take to use various functions and build a report that consolidates instantly.
5
Anonymous Author
Well laid out presentation and well paced. Instructor's used good step by step examples. Information knew for me and had to some of the steps several times. Think course should be rated more than 1.5 credit.
4
Anonymous Author
The information shared was very helpful. I will be challenging my team to take advantage of the information and turn it into actionable reporting.
5
Anonymous Author
This was a lot of information. But very valuable. Thanks for the sample files. I feel like this should be worth more credit hours.
2
Member's Profile
The presenter went to fast and not enough examples.

Prerequisites

Course Complexity: Advanced

Basic Excel and Power Query knowledge.
Example: be able to open one Excel file and connect to external data files, etc.
Recommended prerequisite: Power Query (Part 1): Ultimate Data Transformation

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
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
INTRODUCTION AND OVERVIEW
  0:32Introduction to Power Query 2: Building an Instant Consolidation
  0:37What you will Get from this Course?
  1:14Instructor Introduction
  1:32Comparison with Other Similar Courses
FROM BASIC TO INTERMEDIATE
  1:29Instant Consolidation with Power Query
  1:36Refresher
  3:51Refresher Excel Example
  2:47Formula 1
  2:11Formula 2
  5:20Formula 3
  1:51Formula 4
ADVANCED: 6 POWERFUL FUNCTIONS IN POWER QUERY
  2:10Consolidate Files from Folder
  3:49Append Query
  3:00Fill Down
  3:00Group By
  4:13Parameters Query
  2:20Parameters Query 2
  4:16Reference
Advanced - Take it to the Next Level!
  2:23MTD YTD
  4:06Consolidated Queries
  3:16Dashboard
  4:38Timeline to replace parameters
  6:55"Bot" to automate the refresh!
Conclusion
  1:19Takeaways
  0:42Next Course
CONTINUOUS PLAY
  1:09:07Power Query 2: Building an Instant Consolidation
SUPPORTING MATERIAL
  PDFSlides: Power Query 2: Building an Instant Consolidation
  PDFPower Query 2: Building an Instant Consolidation Glossary/ Index
  XLSXWorkbook: Session 5 - Power Query (Practice)
  XLSMWorkbook: Session 5 - Power Query (Answer)
  ZIPReports to consolidate
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM