Power Query: Ultimate Data Transformation (Video) (Course Id 2213)

• Who is this course for?
  This course is designed for CPAs, accountants, and finance professionals seeking Continuing Professional Education (CPE) who want to improve their data transformation skills using Microsoft Power Query.

• What is this course about or what problem does this course solve?
  The course addresses the inefficiencies of traditional Excel data handling by teaching how to automate data transformation tasks using Power Query, eliminating manual steps like copy-paste and VLOOKUP.

• How can the knowledge from this course be used?
  Participants can apply Power Query techniques to streamline data processing, automate reporting, and perform instant consolidation in Excel, improving overall productivity.

• Why is this course important to a CPA, Accountant, or IRS Enrolled Agent?
  It equips accounting professionals with cutting-edge tools for data manipulation, enhancing their ability to analyze and present financial data more efficiently.

• When is this course relevant or timely?
  This course is timely as of its 2023 release and remains relevant due to the growing reliance on automated and AI-driven data tools in accounting and finance.

• How is a course like this consumed or used?
  The course is delivered as a QAS self-study video module, with review and final exam components, and requires only basic Excel knowledge to begin.

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

• No more copy and paste of data;
• No more links to source files;
• No more repetitive and manual data processing;
• No VLOOKUP needed;
• Brings AI in data transformation;
• Fill the gap in Excel that cannot perform instant consolidation before, as can most cloud-based systems;
• Feed into Pivot table and pivot chart to allow for self-service reporting

Second part of this course is to demonstrate 6 basic skills in data transformation with Power Query:

• Remove columns
• Remove rows
• Promote headers
• Advanced query editor
• Query from Folder
• Data Type

Third, we will accelerate our learning with 3 bonus functions that will dramatically increase our productivity. They are:

• Merge to replace VLOOKUP()
• Unpivot
• Exploration of AI and machine learning enabled in Microsoft Power Query

Last, the course will culminate through the concept of “Instant Consolidation”, which has never been around in MS Excel until Power Query. The instant consolidation is prevalent in cloud-based accounting software.

Video - NASBA QAS - NASBA Registry - 2213

Course Learning Objectives

• Discover state-of-the art data transformation skills,
• Explore the AI (Artificial Intelligence) that Power Query brings to us.
• Discover Power Query’s basic steps in data processing
• Identify 3 advanced functions in Power Query that will enable easy modeling in Pivot Tables

Chapter 1 - Power Query: Ultimate Data Transformation

Chapter 1 Review Questions

Glossary/Index