Book a course
1 day instructor-led course

What you will learn

Excel includes a powerful new feature called Power Query also known as Get & Transform, which provides compelling data gathering and cleansing capabilities. This course shows you the many uses of Power Query by explaining what it is, and how its data handling features differ from the Excel functions.

We look at importing data into Power Query from various sources, and work with Power Query formulas called M to perform powerful data analyses. You will merge and cleanse data from multiple tables, and pivot data to examine it from a variety of angles.

At the end of the lesson we look at using Power Query in Power BI Desktop, which has a rich set of features available for data shaping, enabling the creation of sophisticated data models. Looking at steps required to connect to data sources and review basic data cleansing and modelling.

Prerequisite

Attendees are required to be Excel users before joining this Power Query course.

Course outline

Getting Started with Power Query

  • Power Query concepts and overview
  • The Power Query UI
  • When to use Power Query

Working with Queries

  • Query data from a table or range
  • Query data from another Excel file
  • Query data from a folder and import multiple files
  • Load data only as a connection

Working with Columns

  • Fill up and fill down
  • Split column by delimiter
  • Add conditional and custom columns
  • Add column by example

Working with Formulas (M)

  • Working with formulas in Get & Transform
  • Use IF formulas
  • Nest IF and AND
  • Exact match equivalent of VLOOKUP
  • Approximate match equivalent of VLOOKUP

Pivoting and Unpivoting Data

  • Pivot data in Get & Transform
  • Pivot and append
  • Don’t Aggregate
  • Unpivot

Grouping

  • Group data

Append a Query

  • Two data sets
  • Multiple tables
  • Combing tables with different headings

Merging Data with Joins

  • Overview of joins in Excel
  • Full Outer join
  • Inner join
  • Left Anti join
  • Right Anti join
  • Left Outer join
  • Right Outer join
  • Merge and multiply fields

Putting it All Together and Applying Your Learning

  • Real-world example 1
  • Real-world example 2

Power Query within Power BI Desktop

  • Connecting to data sources
  • The Power Query editor
  • Perform transformations
  • Configure a dashboard
  • Publishing a report to Power BI Pro