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: September 27, 2020

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