Skip to content
Microsoft Excel Power Query course background

Microsoft Power BI training courses

We provide Microsoft Power BI training courses at introduction, intermediate and advanced levels.  Onsite courses, at your offices UK wide, are also available as well as tailored workshops using your own data sets and consultancy.  Choose from classroom training or live online Power BI Desktop courses.

Use Microsoft’s Power BI (Business Intelligence) tools to analyse data sets quickly, provide visual insights via reports and dashboards and to share that data with colleagues.  Learn how to get the most from your data using Microsoft BI.

Microsoft Excel Power Query course logo

Microsoft Excel Power Query course

1 day

In this Microsoft Excel Power Query course you'll learn how Excel gets data from almost anywhere (web, databases, Excel files, SharePoint, Salesforce etc.) and enables you to tidy it up it in many ways (clean, transform, merge and append).  Non-technical Excel users can analyse data and produce reports quickly using it’s GUI interface that’s easier to learn than formulas or VBA.

Download PDF Book now

What will you learn?

At the end of this Microsoft Excel Power Query 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, analyze, and visualize data

Audience?

Anyone using Excel to clean and reformat data to produce reports.

Course Contents

Introduction

  • What is it?
  • Installing Power Query
  • The interface

Common Data Import Sources

  • Working with CSV; Text; Excel Files
  • Importing multiple files
  • Working with web data
  • Scraping Data from web pages
  • Call to a Web Service

Folders and Multiple Files

  • Get data from Windows File manager
  • Combining Data from Multiple Files

Working with Columns

  • Name; Move; Split; Merge
  • Add/Remove; Format; reorder

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
  • Removing spaces, special characters

Table Transformations

  • Unpivoting Columns to Rows
  • Transposing a Table
  • Creating Custom Columns

Loading Data

  • Into a Worksheet
  • Into the Excel Data Model
  • Table Relationships
  • Refreshing Queries Manual & Auto

Query Editor

  • Edit Query Steps and Settings
  • Refresh a Query

Queries

  • Understanding Power Query’s M language syntax
  • Merging tables and queries
  • Using functions in columns
  • Using IF statements
  • Creating custom functions
  • Implementing dynamic parameter tables
  • Creating calendar tables
  • Sharing queries
  • Best practice

Course Materials

Enliten IT will provide each delegate with a workbook and other useful reference materials where applicable.  These may be either paper based or in Adobe pdf format or a combination thereof.

“"Really great training session. Alan was a great trainer, really engaging and made the session easy to follow and understand. The fact it was remote based training as well made the fact that he was able to keep up all following along and engaged throughout even more impressive!"”

Ben, Software One, Power BI Introduction

Prerequisites

Delegates must have advanced knowledge, experience and understanding of PivotTables.

Additional Information

NOTE:   We only offer this Microsoft Excel Power Query course course on a private in-house basis.

Enliten IT reserves the right to modify course content and materials as required in the interests of continuous course improvement, at any time, without notification.

The suggested course duration is a guideline.  Course topics and duration may be modified by the instructor based upon the knowledge and skill level of the course participants.