Master your Excel data

Masterclass description

 

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.

 

Masterclass outline

 

  • Review of Essential tools

    • The blueprint of “good data”

    • Working with Excel tables

    • Working with PivotTables

    • Pivot Table layouts and formatting

  • 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

    • Query structuring

    • Query folding