Power Query and Power Pivot: A Practical Introduction for Finance, Accounting, and Tax Professionals

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) and Power Pivot within Microsoft Excel lets you connect, gather, transform, and combine data very efficiently, which will enable any finance, accounting, tax professionals to automate processes that are very manual and repetitive.

This is an introductory workshop that will help you develop the basic skills to start transforming and analyzing data with Microsoft Excel Power Query and Power Pivot. 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 and Power Pivot. 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.

A more advanced workshop in Power BI will be conducted subsequently if you wish to take your skills on M and DAX further after this session.

What you will learn

By the end of the workshop, you will :

  • Be able to connect and transform data using Power Query
  • Be able to organize data and create relationships between data in Power Pivot
  • Learn the basic skills to start analyzing and visualizing data with Power Pivot

Programme Outline

Connect and transform data using Power Query

  • What is Power Query (M)?
  • Excel workbook
  • CSV
  • Database
  • Folder
  • Web
  • API
  • How do I edit a query?
  • How do I format data?

Advanced data transformation techniques (M)

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

Data modeling and DAX

  • What is data modeling?
  • What are related tables?
  • What is DAX?
  • Traditional Pivot vs. Power Pivot
  • Import a data model to Power BI      

Analyze and visualize data using Power Pivot

  • PivotTables
  • PivotCharts
  • PowerView

Prerequisites

  • A basic knowledge in common Excel functions such as IF, SUMIF, VLOOKUP, HLOOKUP, and
  • A basic knowledge in Excel pivot tables and pivot charts, and
  • A basic knowledge in relational database OR a basic knowledge in Power BI

Laptop Requirements

You are required to bring along an internet-enabled laptop (Windows only)*.

The Laptop should be equipped with Excel 2013 or newer version**

* Power Pivot is not available in Microsoft Excel for Mac.

** Power Query has been renamed and become part of Get & Transform in Excel 2016 and newer versions. Please download and install Power Query for Excel 2013 if you use Microsoft Excel 2013. The steps to download and install are as follows:

  1. Go to the official Power BI webpage (https://www.microsoft.com/en-sg/download/details.aspx?id=39379), select the language, and click “Download” button.
  2. Choose the version (32-bit or 64-bit) that is consistent with the version of Microsoft Excel installed on your machine.
  3. If you are not sure what version of Microsoft Excel is installed on your machine, open Excel and go to File > Options > click “About Excel” button.
  4. A small panel will appear indicating the version of Microsoft Excel installed on your machine.
  5. Click Next button to download the installation file. Once download is complete, run the installation file.
  6. Power Query add-in is now installed. The Trainer will activate the add-in together with you during the class.

Target Audience

Finance, accounting, or tax professionals who would like to develop baseline knowledge and fundamental skills in Power Query and Power Pivot 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. He leads the business intelligence service practice of A Tax Advisor Pte Ltd, and 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.

John utilizes over fifteen years of combined experience in corporate financial reporting and analysis, corporate tax compliance and provision, executive financial dashboard management, technology and performance improvement, data modeling and warehousing, and ERP integration to assist corporate finance and tax functions with improving performance and managing business risk using business intelligence.

Before joining A Tax Advisor Pte Ltd, John was part of EY’s Tax Technology and Transformation group (formerly known as Tax Performance Advisory) in Washington, New York City and Singapore.

Prior to joining EY, John was a financial analyst in charge of the development of Business Objects tools, data modeling and warehousing, and executive financial dashboard management for a global manufacturing company in the United States.

This is a 2-days webinar, to be conducted on 14-15 July 2020

  • Jul 14
    Tue, 1:30 PM SGT - Wed, 5:00 PM SGT Online
    • $363.80 incl. GST (7%)
    • PD hours: 7

Coming Soon

All Coming Soon >>

One-Connection-One-Fee

Our webinars operate on a 'one-connection-one-fee' basis, so you can have your whole team participate together in a boardroom setting for one cost effective price, using one registered log-in connection. The registered attendee will receive a CPD certificate.

Recordings

Like the topic but can’t make the time? Register for the Live Session and you’ll receive the Recording regardless! Recordings are provided for webinars with a duration of 3 hours and less.