Microsoft Excel Power Query

21/07/2017

Excel Power Query Training Course Outline

Microsoft Excel Power Query – Advanced Level Excel

Duration: 1 day

The Excel Power Query gets data from almost anywhere (web sites, databases, Excel files, SharePoint, Salesforce etc.) 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.

Training ObjectivesExcel Power Query

At the end of this course, delegates will be able to:

  • 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

Target Audience

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

Course Contents

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

Scheduled courses are held regularly in Glasgow & Edinburgh, on-site courses by arrangement across Scotland.