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:
o Consolidate various types of data source files;
o Instant consolidation allows accountants to consolidate files in seconds;
o Facilitate self-service reporting by using Macro and Timeline.
o No more need to request for ad-hoc reports from report builders!
Second part demonstrates 7 intermediate formulas in data transformation with Power Query:
o 5 Text Formulas
o Date Formula
o Conditional formula
Third, we accelerate our learning with 6 advanced functions that dramatically increase our productivity. They relate to:
o How to consolidate files from same sources / file types,
o How to consolidate files from different sources / file types,
o How to fill gaps in rows,
o How to SUM without using Pivot Table,
o How to filter rows using a changing value, and
o 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!
Course Search Keywords
– Power Query
– Advanced query editor
– Append Query
– Fill Down
– Group by
– Parameters query
– Reference
– Timeline
– Self-service reporting
– Instant consolidation
Prerequisites and Advanced Preparation
● 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
Learning Objectives
● Deploy a fully instant consolidation of data from different data sources
● Build a fully automated self-service reporting model from scratch
● Recognize 7 essential formulas in Power Query
● Understand 6 powerful functions in Power Query
● Enhance your financial reports with a Timeline that synchronizes your report title and statements
