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