This course is designed for those interested to learn more about Power Query to help you deal with difficult data challenges and have them ready for quick analysis and report.
Read more.Instructor
We are a collective of highly experienced professionals from the UK and North America who have packed decades of practical skills into top courses!
Access all courses in our library for only $9/month with All Access Pass
Get Started with All Access PassBuy Only This CourseAbout This Course
Who this course is for:
- Data Analysts,
- Business Intelligence Analysts
- MI Analysts
- Project Managers
- PMOs
- Accountants Including Finance Partners and Financial Controllers
- Anyone who deals with lots of complex data
What you’ll learn:Â
- Introduction to getting data from various sources and cleaning it up
- Rapidly transpose, pivot and unpivot your data to make it useful
- How you can quickly combine multiple data sets by appending and joining
- Speed up your data queries with duplication and tweaking parameters
- Troubleshoot your queries and set up auto-refreshing of your data
Requirements:Â
- You need to have an intermediate level of skill with Excel and be comfortable with Data Analysis techniques including Pivot Tables, SUMIF/COUNTIF formulas, sorting and filtering
- You will need access to Excel 2010 or greaterÂ
Software version used in the course:Â
- This course was created in Excel 2016
If you have been wasting vast amounts of time at work trying to gather, fix and combine your Excel data before you’ve ever done any analysis and reporting, then let our resident Data expert will show you how Power Query can transform the way you work!
How power query can prevent time wasting
Power Query is the No.1 Excel feature added in the last decade because it can help you get data from various sources, no matter what format it is and clean it all up for you with nothing more than a few clicks! Specifically, there are two ways Power Query saves you vast amounts of time:
- If you have reports that are frequently updated, then you just create a ‘Query’ from a source of data then all you need to do is press refresh every time you need to update, and Power Query will carry out all the little steps needed to clean up your data
- If you work with messy or unstructured data that consumes a lot of your time to clean up, then Power Query has some functions that instantly fix your data such as unpivoting, appending, joining and transposing to name a few
Power Query’s magic is in saving you from all the usual ways to clean up your Excel data that are far too time-consuming. But it’s not just Excel data you clean up, it’s any data from most Databases, SAP, Salesforce, SharePoint and so on. But you need to understand databases so you can be a better bridge between IT and the business.
How this course will help you
This video course is taught by our in-house Data expert Ross Hughes who has over 30 years of experience working with Data in senior technical roles within major companies. In a few short hours of self-paced training, Ross will have you going through the full cycle of ‘Getting, Transforming and Refreshing’ your data, after which it will be ready for quick analysis and report.
Ross’ experience is priceless, he shows you how to harness Power Query like few can and having him teaching you ‘over his shoulder’ is more than worth the price of the course.
Our Promise to You
By the end of this course, you will have learned Power Query in data management.
10 Day Money Back Guarantee. If you are unsatisfied for any reason, simply contact us and we’ll give you a full refund. No questions asked.
Get started today and learn more about how to handle challenging data using Power Query.
Course Curriculum
Section 1 - Getting Started With Power Query | |||
Note Regarding Course Materials | 00:00:00 | ||
Power Query Module 1 Materials | 00:00:00 | ||
Introduction To Power Query | 00:00:00 | ||
Getting Familiar With The Power Query Editor | 00:00:00 | ||
First Data Clean Up For A Pivot Table | 00:00:00 | ||
Tweaking Our Query Until The Data Is Correct | 00:00:00 | ||
Rounding Data | 00:00:00 | ||
Altering Data Types | 00:00:00 | ||
Filtering Out Data Before We Load It | 00:00:00 | ||
Advanced Calculated Columns | 00:00:00 | ||
Grouping Columns | 00:00:00 | ||
Using Columns From Examples | 00:00:00 | ||
Section 2 - Transpose, Pivot And Un-Pivot | |||
Power Query Module 2 Materials | 00:00:00 | ||
Introduction To Transpose, Pivot And Un-Pivot | 00:00:00 | ||
Pre-preparing Messy Data | 00:00:00 | ||
Appropriating Our Data With Transpose | 00:00:00 | ||
Un-Pivoting Data | 00:00:00 | ||
Pivoting Data | 00:00:00 | ||
Creating A Pivot Chart | 00:00:00 | ||
Section 3 - Combining Data From Two Or More Data Sets | |||
Power Query Module 3 Materials | 00:00:00 | ||
Appending Data | 00:00:00 | ||
Introduction To Merging Data | 00:00:00 | ||
Left Outer Join | 00:00:00 | ||
Right Outer Join | 00:00:00 | ||
Full Outer Join | 00:00:00 | ||
Inner Join | 00:00:00 | ||
Left Anti Join | 00:00:00 | ||
Right Anti Join | 00:00:00 | ||
More Appending With Bigger Data Sets | 00:00:00 | ||
Going Deeper With Merging Data | 00:00:00 | ||
Section 4 - Duplication And Parameters | |||
Power Query Module 4 Materials | 00:00:00 | ||
Duplicating Queries | 00:00:00 | ||
Speeding Up Query Editing With Duplication | 00:00:00 | ||
Filtering Duplicated Queries | 00:00:00 | ||
Working With References | 00:00:00 | ||
Parameters | 00:00:00 | ||
Section 5 - Steps, Groups And Dependencies | |||
Power Query Module 5 Materials | 00:00:00 | ||
Functions | 00:00:00 | ||
Dealing With Errors | 00:00:00 | ||
Steps Part 1: Troubleshooting Queries | 00:00:00 | ||
Steps Part 2 | 00:00:00 | ||
Queries And Groups | 00:00:00 | ||
Advanced Query Editor | 00:00:00 | ||
Query Refresh | 00:00:00 | ||
Dependencies | 00:00:00 | ||
Revisiting Combining Queries | 00:00:00 |