Microsoft Excel Advanced: Power Query or Data Transformation
Learn to use the tools designed specifically to make transforming and loading data into Excel streamlined and easily repeatable. Power Query was introduced to us in 2013 and is now firmly entrenched into the Excel interface as the Get and Transform tools. It is a game changer if you need to get data into your spreadsheets for analysis, particularly if the data is not always in the right shape.
Interested in attending any future dates of this course?
Register your interest now
Description
Learn to use the tools designed specifically to make transforming and loading data into Excel streamlined and easily repeatable. Power Query was introduced to us in 2013 and is now firmly entrenched into the Excel interface as the Get and Transform tools. It is a game changer if you need to get data into your spreadsheets for analysis, particularly if the data is not always in the right shape.
Rather than loading your raw data into a worksheet then using a combination of formulae, VBA and manual hacking to get it into a useable state, Power Query makes it easy to create a repeatable process to do this that takes place every time you 'refresh' your connection. Data can come from a variety of sources and be transformed and combined in a number of ways. This is a tool that can save your hours!
Key content
- simple data transformations that include text and dates, and using filtered rows and selected columns to only load the data you need
- 'unpivoting' data into a format suitable for PivotTable analysis
- combining data from a number of files in the same folder into a single table
- combining data from a number of sheets in the same workbook into a single table
- unstacking data from multiple sets of columns
- merge or append data from different sources before loading into Excel
- editing the query language ' M;
Objectives
- be able to transform a variety of data
- load the transformed data into Excel for analysis and easily refresh it
- work with the Query Editor to refine the process
Who should attend?
You should have good intermediate experience in working with Excel. Classes will be held in a school computer suite running Windows and using Microsoft Excel 2016 (part of Office Professional Plus). Please bring a USB stick with you to class to save your work.
Testimonials
You can learn any Excel formulae or function online but setting aside time to focus on an aspect of Excel with Jose is a much better use of time! Her experience, knowledge base, pace of class, and ability to gauge and engage all course participants is no small feat and she does it with such ease. Having attended Jose's Pivot Tables, Data analysis Skills and Data Transformation courses, I'd happily attend any of Jose's tutorials again. As a full-time working mum, I feel very privileged to have attended her half-day Saturday courses. and would gladly enrol in any course that she runs as there are so many nuggets of wisdom and unexplored functions and formulas that make Excel more accessible. I feel so much better equipped with skills and shortcuts that make my work more efficient. Jose makes Excel interesting, accessible, way less challenging and full of un-tapped opportunities. There are so many transferrable Excel skills that Jose can teach that can be applied to other functions such as Power BI, reporting and Data analysis. Ming, November 2022
