Excel Power Query

Who is this course for?

This course will benefit:

• People who work with reports, dashboards, and analysis in Excel and spending hours converting, cleaning, formatting, deleting, changing structure in source data from other Excel worksheets, financial systems, databases, websites, SharePoint, and many other sources.
  
• Those using Vlookup and other lookup and reference function to merge data.
  
• All, who want to automate the process working with external data in Excel.
  
• Everybody, who need to combine data to use in Excel.
  
• All, who get data from CSV files, text files, XML files, or other excel workbooks.
  
• Everyone, who would like to drop source data files into a folder and get the Excel report/dashboard updated automatically from the data in the new files, added to the folder.
  
• Everybody working with data models in Excel can use Power Query to extract, clean, combine, merge, and aggregate data before adding the data to a data model.
  

Prerequisites

An advanced knowledge of Excel.

Benefits

Getting started

Use Power Query in different versions of Excel
Navigate and get a basic understanding of Power Query
Understand Applied Steps in Query Settings

Extract Data

Extract data from excel table
Extract data from excel external workbooks
Extract data from databases
Extract data from web
Extract data from other sources
From folder (multiple files)

Transform Columns & Add Columns

Use Group By to group source data
Use first row as headers
Aggregate columns
Calculate columns
Use text functions in columns

Clean Data

Change text to columns
Remove unwanted columns and rows from data source
Cope with formatting issues
Manage are source data cleaning issues
Unpivot data
Replace errors or values in columns

Merge & Append

Merge data from an Excel workbook
Merge data from multiple Excel workbooks
Merge data from multiple data sources
Append data from Excel worksheets and multiple data sources

Automate Queries

Understand Power Query technics to automate queries
Understand how to create simple functions in Power Query
Create Dashboard/Report from Power Query data

Face-to-face training

Training is held in our modern, comfortable, air-conditioned suites.

Modern-spec IT, fully networked with internet access

Lunch, breaks and timing

A hot lunch is provided at local restaurants near our venues:

• Bloomsbury
• Limehouse

Courses start at 9:30am.

Please aim to be with us for 9:15am.

Browse the sample menus and view joining information (how to get to our venues).

Refreshments

Available throughout the day:

• Hot beverages
• Clean, filtered water
• Biscuits

Virtual training

Regular breaks throughout the day.

Learning tools

In-course handbook

Contains unit objectives, exercises and space to write notes

Reference material

Available online. 100+ pages with step-by-step instructions

24 months access to Microsoft trainers

Your questions answered on our support forum.