Excel: Power Query

Description

Power Query: your tool to automate lists!

You often have to re-edit the data you enter into PowerPivot: splitting up the text into different columns, converting text to upper case, adding extra columns with certain calculations, filling up blank cells and much more.

This means that you can’t use certain sources straight away, because you first need to delete unnecessary information. Or because that information has a different layout. With the aid of Power Query you can get round these problems without difficulty. You input the data, define what is to be done and then enter the optimised data, in Excel, or in the PowerPivot data model. And this without programming in VBA!

Objectives

After this Power Query training you will be able to:

Input different data sources in Power Query
‘Mould’ data so that it is ready to be analysed
Enter data in Excel or in the PowerPivot data model

Target Group

Anyone who ends up having to re-edit data before it can be used in Excel or in PowerPivot .

Prerequisites

A solid knowledge of Excel is required. Knowledge of PowerPivot is not required.

Content

Part 1: Power Query

Why use Power Query instead of Excel functions?

Part 2: Importing Data

Importing tables from the Excel file
Getting external data (Excel, TXT, CSV, all files in a folder, SQL…)
Data source settings (storing login credentials from previous connections)

Part 3: Uploading 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, deleting records, filtering, aggregating, handling blanks, unpivoting
Adding columns (using menu options, column from examples, custom calculations)
Extending queries with data from other queries (like VLOOKUP/XLOOKUP)
Comparing queries (common values, differences)
Merging similar queries (appending)