Description

Power Query, your tool for automating lists!

The data you load into Power Pivot often still needs work: splitting text into separate columns, converting text to uppercase, adding extra columns with certain calculations, filling blank cells, and much more.

On top of that, some sources can't be used right away because you first need to remove unnecessary information, or because that information has a different layout. Power Query lets you get around these problems effortlessly. You read in the data, define what needs to happen, and then load the optimised data into Excel or into the Power Pivot data model. All without any VBA programming!

Objectives

After this Power Query training you'll be able to:

• Import various data sources into Power Query

• 'Shape' data so it's ready for analysis

• Load data into Excel or into the Power Pivot data model

Target audience

Anyone who has to process the same data repeatedly before it can be used in Excel or Power Pivot.

Prerequisites

Solid knowledge of Excel is required. Knowledge of Power Pivot is not required.

Contents

Part 1: Power Query

• Why use Power Query instead of Excel functions?

Part 2: Importing data

• Importing tables from the Excel file

• Retrieving external data (Excel, TXT, CSV, all files in a folder, SQL...)

• Data source settings (saving login details from previous connections)

Part 3: Loading data

• To a table on a worksheet

• To Power Pivot

• Choosing not to load queries

Part 4: Transforming data

• Converting formats (US/European), changing data types, removing duplicates, removing records, filtering, aggregating, handling spaces, unpivoting

• Adding columns (using menu options, columns from examples, custom calculations)

• Extending queries with data from other queries (like VLOOKUP/XLOOKUP)

• Comparing queries (common values, differences)

• Merging similar queries (appending)

Part 5: M Language

• Introduction to the M formula language for advanced queries