Power Query is an Excel tool that lets you automatically import, clean, transform and combine data from different sources. Learn how to merge multiple files, perform date calculations and create dynamic filters. You will save time by making your tables reliable and relevant, and by automating repetitive tasks in Excel.

Beginner

Completion of the Advanced Excel course or equivalent knowledge
A computer and Microsoft Excel (versions 2013, 2016 or Office 365)

Analysis, accounting and finance professionals, and anyone who wants to save time and improve their data-handling skills.

• Clean and format data.
• Transform data (pivoting rows and columns, duplicates, grouping).
• Replace a VLOOKUP with a merge.
• Simplify and automate appending and combining sheets.

Introduction

• The four phases of Power Query
• Overview of the interface
• Notes and recommendations
• Connecting to a data source

A. Formatting and cleaning

• Split a column
• Merge columns (“Concatenate”)
• Extract the contents of a column
• Fill empty cells
• Change the case
• Replace one value with another
• Format dates

B. Transformation

• Transpose columns into rows, and vice versa
• Convert to a list
• Find, then remove or keep duplicates
• Group data (“Aggregates”)

C. Date calculations

• Calculate the number of calendar days between dates
• Calculate the number of working days between dates

D. Merging data (VLOOKUP)

• Merging sheets within the same workbook
• Merging sheets across different workbooks

E. Appending and combining sheets

• Append and combine the sheets of a workbook
• Automate appending and combining the sheets of a workbook
• Append and combine sheets from multiple workbooks stored in the same folder whose sheets have the same name
• Append and combine sheets from multiple workbooks stored in the same folder whose workbooks and sheets have different names

F. Setting up a dynamic filter on a table

G. Changing the path of a source file

• Manually
• Use a cell as a data source for Power Query
• Use a cell as a data source for Power Query by breaking down the path, file and sheet

Questions and answers