Get Power with Power Query & Transform your Data

Masterclass Overview

The sad reality is that not all data is stored in nicely curated databases and often – even when it is – the data analyst doesn’t have access.  Instead we have to piece together data provided in text files, Excel files, web pages and even the body of emails to build the business intelligence solutions we need. Until now this has been a painful process with a great deal of cut and paste effort, and updates have been tedious and error prone. That stops today.

In this course, you’ll learn how Power Query can clean up, reshape and combine your data with ease – no matter where it comes from. Converting ASCII files into tables, combining multiple text files in one shot and even un-pivoting data is not only simple, but an investment in the future refreshable with a single click when next needed.

If you need to learn one skill in Excel today, how to work with Power Query is it.  Not only will it change the way you “Get & Transform” data in Excel, but it’s also the system used to collect data for Power BI desktop, meaning these skills are transferable to other programs.

Target Audience

Anyone who needs to pull data into Excel, clean it up and/or consolidate it. Experience working with Pivot Tables is an asset, but not required.

Software Requirements

Power Query is built in to Excel 2016.  For Excel 2010 and 2013, you’ll need to ensure that you have the free Power Query add-in installed.*

*Note that depending on your version of Excel 2013, you may not be able to connect to some “business” data sources like Microsoft Exchange, SQL Azure or Sharepoint.  All other data sources demoed in the course are available regardless of the Office 2013 version you have.

Time Commitment

This course is a hands-on course and runs approximately 8 student’s hours (45 minutes) in length, depending on the class size and attendees experience in working with Excel.

Masterclass At A Glance

Review of Essential Tools

  • The blueprint of “good data”
  • Working with Excel tables
  • Creating basic PivotTables

Importing Data

  • Individual CSV, text and Excel files
  • Individual Non-delimited text files
  • Importing multiple “flat” files at one time
  • Cleaning and manipulating data
  • Refreshing imports

Appending and Merging Tables

  • Append (stack) data from multiple tables
  • 7 ways to merge (join) data from multiple tables (with no VLOOKUPs)
  • Many to many merges

Pivoting, Un-Pivoting and Transposing Data

  • Un-pivot tables with ease
  • Pivoting stacked data
  • Understanding the Transpose feature
  • Un-pivoting subcategorized data
  • Grouping data

Conditional Logic

  • Creating conditional columns
  • Manual IF and IFERROR tests
  • Creating columns from example

Best Practices

  • Understanding Data Privacy
  • Query structuring
  • Avoiding the formula firewall