Get & Transform- Excel’s data cleanup tool (aka PowerQuery)
Get & Transform- Excel’s data cleanup tool (aka PowerQuery)
The most common task Excel is used for is to ‘clean’ or ‘scrub’ data received from other systems to be usable in reports and dashboards.
Whether you are doing reconciliations or generating BI reports, you need data from external sources over which you have limited format and data type control.
- Time Req – 8 hrs
- Skill Score >4
- FAQ
- All Online Courses
- Live Course Options
- Ask a Question

Although Excel has many inbuilt tools, a new, and very powerful tool is the free PowerQuery (called Get and Transform in recent versions) tool.
As the latest name suggests, it allows you to GET data from a multiple of sources and (easily) TRANSFORM it into any format you need.
This course is an online training and reference guide.
Request info on Online Courses (T&C’s, discounts etc)
By the end of the course you will be able to:
- Reformat data to be able to create business intelligence reports, dashboards or reconciliations.
- Split columns into names and surnames, product codes and product names etc
- Delete rows that contain unnecessary headers, footers, subtotals and other unwanted information.
- Change dates so that Excel recognises them
- Fill in gaps in reports
- Combine multiple CSV’s (and other files) into a single file
- Unpivot reports
- Automate the running of all the above to make it repeatable.
This course will help you with all these data cleanup in Excel issues. At the end of it you should be able to click refresh, go have some coffee and come back to a completed report!
Modules
Data Cleanup Fundamentals
Lessons
Get & Transform introduction
Lessons
Get & Transform Ribbons
Lessons
GET data into PowerQuery (Get&Transform)
Lessons
Transform Data introduction
Lessons
Transform Text
Lessons
Transform Numbers
Lessons
Delete Unnecessary Rows
Lessons
Fill in gaps in row of data
Lessons
Convert 'text numbers' into numbers
Lessons
Identify/ remove duplicates
Lessons
Splitting and Merging columns
Lessons
Transpose Data (rows to columns and columns to rows)
Lessons
Compare/ reconcile lists
Lessons
- Compare lists to each other
- Left Outer- All from left matching from right
- Right Outer- All from right matching from left
- Full Outer- All from left and right
- Inner- Only the common items
- Left Anti- All from left that are NOT in right
- Right Anti- All from right that are NOT in left
- Merge Queries Examples