Hello Excel Power Query, My New BFF

Course

Ready to take your Excel experience to a whole new mind-blowing level? Power Query is one of the smartest and most powerful tools ever developed for Excel. Power Query automates – yes, automates -- routine data transformation (e.g. merging/splitting/removing columns, filtering data, replacing data, rounding, sorting, converting text to numbers and dates).

Excel guru Dianne Auld will discuss how Power Query works, how to add it into earlier versions of Excel and the difference between Power Query, Power Pivot and Power BI.  Standard in Excel 2016/2019 and easily installed as a free add-in to Excel 2010 and 2013, Power Query has astonishing capabilities for extracting, transforming and loading data. Power Query can merge and transform multiple files in a folder, then add / transform another file from the folder simply by clicking Refresh.  It can scrape data from web tables, transform the data for use in an Excel file, then update it with a single click. Learn how to use Power Query to automate the transformation of routine payroll/HRIS data, fix problem source data for use in analyses and calculate medians per gender/job title.

In this class, Dianne will demonstrate four applications of Power Query:

  • How to massage an employee data set including filtering and sorting the data, removing columns, calculating age, time in job and extracting name from email address; then use this data to calculate medians per gender/job title, something which is not easy to do using standard Excel functionality; how to create pivots from the data, then refresh all queries and pivots with a single click
  • How to scrape data off a website, and massage the data for use in analysis
  • How to unpivot problem sales commission data for use in analysis
  • How to merge and transform three monthly files of labor turnover data for use in a pivot table, then load/transform the next three monthly files and update the pivot table with a single click.

Bonus:  Attendees will be given all the Excel spreadsheets demonstrated and detailed instructions for replication of the Power Query functions.

Note: The instructor will demonstrate the functionality using Excel 2019 and the Data, Get and Transform menu.

Learning Options

Virtual Classroom

Live online instructor-led delivery of course materials including real-time interaction with a subject-matter expert and peers — no travel required!

Course

Member: $395 USD

$525 USD

Instructor

Dianne Auld

Excel Expert Instructor
Auld Compensation Consulting

Dianne Auld, CCP, GRP, CSCP, WLCP owns a consulting practice, Auld Compensation Consulting, in Cape Town, South Africa. She consults a wide range of organizations across Africa and the Middle East in all areas of total rewards. She has developed and taught courses in Africa, the Middle East, Europe, and the United States.

Dianne is also a faculty member at WorldatWork and a reviewer for the WorldatWork Journal. Well respected and known as an expert in the compensation field and Excel, Dianne's skills and expertise are in high demand. In fact, her sessions at the WorldatWork Total Rewards Conference and Exhibition typically fill to capacity. Diane has partnered with WorldatWork to develop several educational products to assist compensation professionals with their day-to-day tasks in Excel, including "Pay Structures - Develop a Framework from Start to Finish," "Excel Skills for Compensation Professionals," "Advanced Excel Skills for Compensation Professionals," "Excel Tips for Compensation Professionals," and "Tackling Compensation's What If Questions Using Excel".

Course Credits & Certifications

  • Recertification
    .25 Credits

More Information

How to Register

Policies

Click Here to view our policies on payments, returns, class schedules, registration, cancellation, and more.

Have Questions?

Phone

+1 877 951 9191

USA and Canada

+1 480 951 9191

Other Countries

Online

Email Us