+ 27 (0)10 110 0296    :   +27 (0)11 803 4216    info@m-cot.com

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