Microsoft Excel Power Query

Subject

In this Power Query course, you will learn a process of importing data, appending and merging tables, conditional logic, data transformation, and organization. The course starts with the basics of Power Query and works its way up. This means that you'll be a pro in no time, even if you're a beginner with the tool.
The Excel Power Query gets data from e.g databases, Excel files, and enables you to manipulate it in many ways (clean, transform, merge and append) using a straightforward interface add-in to Microsoft Excel. Non-technical Excel users can access large corporate databases to conduct analysis and produce reports.

Course Objectives

Create effective and professional reports
Gather and transform data from multiple sources
Discover and combine data in mashups
Learn about data model creation
Explore, analyse, and visualize data

Duration

1 Day

Pre-requisites

Excel Advanced

Target Audience

Experienced Excel users wishing to create complex reports, or needing advanced data analytics to process, analyse and display data.

Course Content

Introduction to Power Query

Installing Power Query Create and Edit a simple query

Common Data Import Sources

Working with CSV; Text; Excel Files
Importing multiple files
Working with Web data
Scraping Data from Web Pages
Calling a Web Service
Finding Data using a Power Query Online Search

Working with Folders and Multiple Files

Using data from Windows File manager
Combining Data from Multiple Files

Working with Columns

Name; Move; Split; Merge

Filtering and Sorting

Using Auto-Filter
Using Number, Text and Date Filters
Filtering Rows by Range
Removing Duplicate Values
Filtering out Rows with Errors
Sorting
Grouping rows

Changing Values in a Table

Replacing Values
Transformations: - Text; Number; Date/Time
Replacing Missing Values

Table Transformations

Un-pivoting Columns to Rows
Transposing a Table
Creating Custom Columns

Loading Data

Loading Data into a Worksheet
Loading Data into the Excel Data Model
Power Query and Table Relationships
Refreshing Queries Manual & Auto

Query Editor

Edit Query Steps
Edit Query Step Settings
Refresh a Query

Queries

Understanding Power Query's language syntax
Merging tables and queries
Using Power Query functions in columns
Using conditional (IF) statements
Creating custom Power Query functions
Implementing dynamic parameter tables
Creating calendar tables
Sharing queries
Best practices for Query organization

...back to ITC Training