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: March 27, 2024
11 Reviews (64 ratings)

Reviews

5
Member's Profile
Power Query is very powerful in cleaning data and Lenny does a great job explaining what can be done.
5
Anonymous Author
This is a great course for continuing to learn about Power Query. Lenny Wu is a great instructor. There is a lot of information in this course. I could use more courses just going over how to work in Power Query. There is a lot of functionality.
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
Good course. Power query seems very complex, but I’m excited to try it in real life. The VBA is really confusing. Need more explanation of that.
4
Anonymous Author
A Timeline Slicer allows report users to freely select a date/month/quarter/year/period for easy filtering of financial and operational data.
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.
4
Anonymous Author
This course was helpful, but got pretty advanced towards the end and made it a little hard to follow.
5
Anonymous Author
Excellent course but Mr. Wu's pace was way too fast. Credit hours should be more than 1.5.
2
Member's Profile
The presenter went to fast and not enough examples.
show all reviews
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 .
Instructor for this course
  Course Overview CPE
Course Syllabus
INTRODUCTION AND OVERVIEW
  Introduction to Power Query 2: Building an Instant Consolidation0:32
  What you will Get from this Course? 0:37
  Instructor Introduction 1:14
  Comparison with Other Similar Courses 1:32
FROM BASIC TO INTERMEDIATE
  Instant Consolidation with Power Query1:29
  Refresher1:36
  Refresher Excel Example3:51
  Formula 12:47
  Formula 22:11
  Formula 35:20
  Formula 41:51
ADVANCED: 6 POWERFUL FUNCTIONS IN POWER QUERY
  Consolidate Files from Folder2:10
  Append Query3:49
  Fill Down3:00
  Group By3:00
  Parameters Query4:13
  Parameters Query 22:20
  Reference4:16
Advanced - Take it to the Next Level!
  MTD YTD2:23
  Consolidated Queries4:06
  Dashboard3:16
  Timeline to replace parameters4:38
  "Bot" to automate the refresh!6:55
Conclusion
  Takeaways1:19
  Next Course 0:42
CONTINUOUS PLAY
  Power Query 2: Building an Instant Consolidation1:09:07
SUPPORTING MATERIAL
  Slides: Power Query 2: Building an Instant ConsolidationPDF
  Power Query 2: Building an Instant Consolidation Glossary/ IndexPDF
  Workbook: Session 5 - Power Query (Practice)XLSX
  Workbook: Session 5 - Power Query (Answer)XLSM
  Reports to consolidateZIP
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam