Power Query

What is Power Query?
Power Query has been described as the best transformation and data manipulation tool for Excel data. It is a standard tool within Excel 2016, and is also available as a downloadable add-in for Excel 2013. It is an intuitive tool to bring in data from many different sources, such as text files, database tables, SQL data, and other Excel files; it can also import data directly from Internet tables. Power Query helps to clean data when merging from various sources.

Who Should Attend?
To attend this session, you should be a regular Microsoft Excel user.

Course Outline

Objectives:

At the end of this session you will be able to:

  • Import data to Excel from exported raw data from various sources
  • Clean up raw data by eliminating unnecessary rows and columns
  • Append 2 or more Excel files together
  • Merge 2 or more Excel files together.
  • Schedule the refreshing of data import

Duration:

This course is 3 hours in duration and covers the topics in the course outline below.

Outline Topics:

Importing data to Power Query from Excel, and other sources
Promoting headers
Changing data types
Deleting unnecessary columns
Deleting unnecessary rows
Replacing data
Trimming data
Splitting columns
Joining columns
Creating a calculated column
Loading to Excel as a Table
Loading to Excel as a Pivot Table
Changing the source file(s)
Refresh data to insure your results are up-to-date
Setting a schedule for automatic updates
Appending 2 or more Excel files
Merging 2 or more Excel files