Study Method
Blended
Start Date
Feb 10 2022
Length of Course
TBA
Fees
195 per person
Location
TBA
Using Excel Power Query to Transform your Reward Work
EXCEL FOR REWARD – MASTERCLASS #3
Using Excel Power Query to Transform your Reward Work
Part of our Excel for Reward Masterclass series: click here for more details about the five afternoon sessions.
Date: Thursday 10 February 2022
Time: 1pm to 3pm (UK)
Duration: Two hours including five-minute break at approx. 2pm
Venue: Your PC
Watch Dianne demonstrate one of the smartest and most powerful tools ever developed for Excel. Power Query comes standard with Excel 2016 / 2019 / Office 365, and is available as an add-in to Excel 2010 / 2013.
Dianne will show you how it can automate routine data transformation (e.g. merging / splitting / removing columns, filtering data, replacing data, rounding, sorting, converting text to numbers and dates). She will demonstrate how you can achieve this without using macros and VBA, and with hardly any formulas.
Almost all the transformation is done through the simple use of a menu and a mouse.
Dianne will show you how to merge and transform multiple files in a folder, then how to add / transform another file from the folder simply by clicking Refresh. You can scrape data from web tables, transform the data for use in an Excel file, then update it with a single click.
Dianne will demonstrate:
- How to merge and transform three monthly files of labour 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.
- How to calculate medians per gender / job title.
- How to scrape exchange rate data from the web, use the data in a formula to convert multi-country salaries to a single currency, and update data with a single click.
- How to pivot employee salary data into different business units for use in scatter charts and pay structure design.
- How to unpivot problem sales commission data for use in analysis.
- Power Query’s AI capability of adding a column based on examples.
“Reward Blueprints: 'Just like to reiterate what a great service this is and look forward to another 12 months of highly useful research reports.'”
“Reward Blueprints: 'Much of the inspiration for the total reward statements in the organisation , which were nominated for an award this year, came from the invaluable material in”
“Excel April 2015: 'Valuable course, fantastic knowledge of the trainer. Very helpful, small group, learnt lots of quick short cuts and extremely valuable time-saving techniques.' ”
“GRP: 'The course went very well. The tutor was very nice and the group a really good mix of people. I’d definitely like to join another one. Also the organisation was very smooth.”
“GRP: 'I thought the tutor was excellent. A positive experience all round!'”
“ Annual conference 2015: ‘A great overall event. A good cross section of topics and speakers.’”