Excel 2019, 365 – Get & Transform Data (Power Query)

Goals

Connect to data, transform it and display the results in Excel.

Prerequisite

Very good Excel skills

Contents

Connect to data

• Connect to Excel data

• Connect to flat text files (csv, txt)

• Connect to a database

Load data to the workbook

• Load a query to a worksheet table

• Create a connection without loading the data

• Create a PivotTable based on a Power Query connection

Simple transformations

• Delete rows and / or columns

• Pivot / unpivot columns

• Sort and Filter

• Split columns

• Change a column’s data type

• Extract characters from a column

Combine data

• Merge two tables based on a common column

• Append the contents of a table to another table

Advanced transformations

• Add a column from a sample of the expected result

• Create a formula using the editor

• Use parameters

• Create a custom Power Query function