Microsoft Power Query: A Practical Introduction to Excel Workbook Automation

Description

Microsoft Excel is one of the most important computer applications for accountants and finance professionals at all levels.

Power Query (Get & Transform in Excel 2016 and newer versions) within Microsoft Excel lets you connect, gather, transform, and combine data very efficiently, which will enable any finance, accounting, tax professionals to automate processes within Excel that are very manual and repetitive.

This is an introductory workshop that will help you develop the basic skills to start transforming data and automating Excel workbook using with Microsoft Power Query. After completing this workshop, you will be able to get data from various sources, prepare and analyze large amount of data efficiently using Power Query. With his background in Accounting and Tax, the Trainer will be using Profit and Loss, Balance Sheet or POS data in the examples and hands-on exercises. This will facilitate learning and allow you to directly apply your knowledge learned in the workplace.

Another workshop on Power BI will be conducted subsequently if you wish to develop your DAX and data visualization skills further after this session.

What you will learn

By the end of the workshop, you will:

• Be able to connect data to different sources using Power Query
• Be able to transform data using Power Query
• Learn the basic skills to start analyzing and visualizing data with Power Pivot or Power BI

Programme Outline

Connect and transform data using Power Query

• What is Power Query ?
• Excel workbook
• CSV/Text
• Database
• How do I edit a query?
• How do I format data?

Connect and transform data using Power Query

• PDF
• Webpage
• API
• Folder

Advanced data transformation techniques (M)

• Merge vs. append
• Merge vs. data modelling
• Cardinality
• Pivot and unpivot
• Parameter
• Custom function
• Workflow considerations

Analyze and visualize data using Power Pivot

• Analysing and visualizing in Excel
• Analysing and visualizing in Power BI

Laptop Requirements

You are required to bring along an internet-enabled laptop (Windows only)*.
Your laptop should be installed with one of the following Microsoft office products**:

Office 2016

• Home & Student
• Home & Business Standard
• Professional

Office 2016

• Professional Plus
• Standalone

Office 2019

• Home & Student
• Home & Business Standard
• Professional

Office 2019

• Professional Plus
• Standalone

Microsoft 365

• Home
• Personal
• Business
• Business Premium

Microsoft 365 Apps for enterprise

• Office 365 Pro Plus
• Office 365 Enterprise E3
• Office 365 Enterprise E5

To check which Office product is installed on your laptop:

1. Open Excel and go to “File”.
2. Click “Account” at the lower left-hand corner.
3. The license information will be displayed under "Product Information"

* Please note that Power Query is NOT available in Excel 2016 and Excel 2019 for Mac.

** Power Query is available in Excel 2013 as a separate add-in, which can be downloaded from the Microsoft website (Installation instructions will be sent to you upon request).

This workshop qualifies for 7.0 CPE hours in Information Technology (Category 5).

Prerequisites

A basic knowledge in common Excel functions such as IF, SUMIF, VLOOKUP, HLOOKUP

Target Audience

Accounting & Tax Professionals, Business Managers and anyone who would like to develop baseline knowledge and fundamental skills in Power Query within Microsoft Excel and learn about different ways to automate processes.

Expert Speaker

John Kim

John is a US Certified Public Accountant licensed in New York State, and currently leads a Finance Transformation Advisory. He provides a variety of solutions (e.g., technology and data strategy, data transformation and integration, process improvement and automation, and data analytics services) and business process outsourcing services to corporate finance and tax departments. He was formerly a director of EY’s Tax Technology and Transformation group (formerly known as Tax Performance Advisory) in Washington DC, New York City, and Singapore.

John utilizes over fifteen years of combined experience in corporate finance/tax technology and performance improvement, finance/tax operating model design, finance/tax technology roadmap design, executive financial dashboard design and management, finance/tax data modeling and warehousing, ERP finance/tax integration to help corporate finance and tax functions improve performance and manage business risks using business intelligence